> -----Original Message-----
> From: dcabbar [mailto:dcabbar@...]
> Sent: Wednesday, 31 December 2003 10:23
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Full-text Search Support


> Hi All,

> Does firebird support full-text/fuzzy search in text columns? i.e. similar

to my sql's match ... against? Of > course, I am not talking about "like"
searches where you can perform starts with type searches.

> If there is such a functionality, how efficient/good is this?

> Any comment is appreciated...

> TIA

Out of the box fuzzy matches: not yet, but it's certainly possible!!!

There's an article on building a soundex UDF on the ibphoenix site.
http://ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_howto4

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
brute-force scan:
Select * from table where str_text like '%searchterm%'; (Anywhere in text,
brute force)
Select * from table where str_text like '%searchterm'; (Ending with text,
brute force)

'Containing' will never use an index, (AFAIK), as it always scans the entire
field for possible substring matches (same as LIKE '%searchterm%', but case
insensitive).

Your best bet: roll a soundex or metaphone UDF(or find one that's already
written)

Disclaimer: I could be wrong on any of these points...

Nige.