Subject Re: [ib-support] Re: Case sensitivity drives me crazy
Author Helen Borrie
At 05:53 PM 20-12-01 +0000, you wrote:
>Thank you.
>I use Java so that I could not use IBObject. Helen, could you
>elaborate how to use "Soundexes"

Without embarking on a tutorial, a Soundex is a code that is calculated on the aurally significant pieces of a string (consonants plus a couple of significant vowels). Smith, SMITH, Smythe will all get the same Soundex code, typically a smallint.

With Firebird/IB all you need is a UDF that calculates Soundexes. There is one in the FreeIBUDF library which is available for Windows servers, at least. I expect there are others around. You need triggers (BI, BU) to calculate and populate your soundex columns (one for each search column, indexed) when rows are inserted or updated.

Then, when you are searching for the names, your SQL calls the UDF to soundex the search string and selects the records with matching soundexes:


It's slightly complicated to set up (the first time, anyway) but worth the effort for huge search bases.

btw, an Altavista search request on "Soundex" yielded 25 pages of entries, a lot of them on-line soundex search demos.