Hi Rolf,

> Can someone tell me where i can look for an example
> which explains the IBO-SOUNDEX functions?

I dont know of any prebuilt example database but...

There is an article at: http://www.ibphoenix.com/ibp_howto4.html
which explains the use of soundex in database terms. I dont think
this is the same article that used to exist on the old interbase
website, but I cant find that any more.

Note that that example uses a smallint value for the soundex index,
you can find compatible code in the FreeUDFLib.

The soundex functions that are part IBO are my extension to the
standard soundex concept described in the above article but extended
to use 32bit integer value and to support partial input values. You
can find a description of these functions by looking up TC_SoundEx in
the IBO online help (or browsing the IB_Utils.pas unit).


A brief summary:

At the server...

1. At the server implement a UDF to provide soundex capability.

2. On the table where you want soundex support create a field to store
the soundex value (either smallint or integer depending on which
algorithm you use).

3. create triggers that define the soundex field value:
Soundex_Field = f_soundex(Name_Field);

4. create an index (non-unique) on the soundex field


On the client...

A. make the soundex functions available (optional)

B. add event handler TIB_Connection.OnSoundExParse

C. if using TC_SoundEx also define TIB_Connection.OnSoundExMaxParse so
that it uses TC_SoundExMax

D. setup the query columnattributes to tell IBO which field has a
soundex duplicate. eg;
Name_Field=SOUNDEX=Soundex_Field;


I've only just realised that IBO is setup so that installing the
soundex functions in the client is optional! To explain; If the
soundex functions are on the client then the OnSoundExParse handler
would look like...

procedure Form1.IB_ConnectionSoundExParse(Sender: TObject;
SourceStr: string; var ResultStr: string )
begin
ResultStr := IntToStr( TC_SoundEx(SourceStr) );
end;

however it looks like (I have not actually tried this) you can also do
something like...

procedure Form1.IB_ConnectionSoundExParse(Sender: TObject;
SourceStr: string; var ResultStr: string )
begin
ResultStr := 'f_soundex(' + QuotedStr(SourceStr) + ')';
end;

where "f_soundex" is the name of the soundex UDF available on the
server. So this implementation would pass the soundex calculation to
the server so that you would not need the function to exist in the
client application.


Thats about it. If you use the FreeUDFLib you do not need to define
OnSoundExMaxParse, this was defined to support the extended feature of
TC_SoundEx and TC_SoundExMax. To explain...

With the standard soundex you can only search for a complete entry (or
at least complete to the number of characters that are used by
SoundEx). So if a user types 'smi', the standard soundex can only
search for names that sound like that (as though the name sounds that
long). So if NOTRAILING is specified for the field the SQL results in
something like...
WHERE Soundex_Field >= f_soundex('smi') and
Soundex_Field <= f_soundex('smi')

My extensions to soundex support the ability to use 'smi' as a
starting value, so that (if NOTRAILING is specified) a search will
return all names that sound like they START with 'smi'. The SQL
results in something like...
WHERE Soundex_Field >= f_soundex('smi') and
Soundex_Field <= f_soundexmax('smi')

where the f_soundexmax udf (the same as the TC_SoundExMax function in
IBO) returns the maximum possible soundex value of a string that
starts with 'smi'.


I hope this all makes sense. Perhaps I will get around to creating an
example application one day, but no time right now.


Geoff Worboys
Telesis Computing