|Subject||RE: [firebird-support] Full-text Search Support|
> -----Original Message-----to my sql's match ... against? Of > course, I am not talking about "like"
> From: dcabbar [mailto:dcabbar@...]
> Sent: Wednesday, 31 December 2003 10:23
> To: firstname.lastname@example.org
> Subject: [firebird-support] Full-text Search Support
> Hi All,
> Does firebird support full-text/fuzzy search in text columns? i.e. similar
searches where you can perform starts with type searches.
> If there is such a functionality, how efficient/good is this?Out of the box fuzzy matches: not yet, but it's certainly possible!!!
> Any comment is appreciated...
There's an article on building a soundex UDF on the ibphoenix site.
As for built-in functions, I've heard there's a double-metaphone function in
the works, somewhere...
As with most of these type of searches, it pays to store the hash of the
word before a search, i.e.:
Select * from table where dmphone(str_text) = dmphone(searchterm)
Would be shooting yourself in the foot, as it would have to convert every
single record to see if it matched.
On the other hand, if a trigger calculated the dmphone, and stored it in
it's own field, with an index on it, it'd fly:
Select * from table where dm_text = dmphone(searchterm)
Then there's ones you already know about: 'like', and 'containing'
'like' can use an index, if you search from the first character onwards:
Select * from table where str_text like 'searchterm%'; (Starting with
text, uses index)
But if you move it away from the first character, suddenly it has to do a
Select * from table where str_text like '%searchterm%'; (Anywhere in text,
Select * from table where str_text like '%searchterm'; (Ending with text,
'Containing' will never use an index, (AFAIK), as it always scans the entire
field for possible substring matches (same as LIKE '%searchterm%', but case
Your best bet: roll a soundex or metaphone UDF(or find one that's already
Disclaimer: I could be wrong on any of these points...