Hi all

I want to construct a query that will look for all peoples names that
start with a given string or contain a name sounding similar to the
string...

For example, if the user wants to find 'Smith' I would like to return
all the 'Smith', 'Smith & Smith', 'Smith's Shoe Factory', 'Smythe',
'Smythe's shoe factory', etc.

I have a column that contains the soundex index of the name, and one
that contains the uppercase version of the name, so my query looks a bit
like:

Select * from Contact
where
UC_Name Like 'SMITH%'
OR
Soundex_Name = F_GenerateSndxIndex('Smith')

This query is a bit slower than what I had thought, for example, the
time taken to a Select * from Contact where UC_Name = 'SMITH%' plus the
time taken to do the other soundex query separately is quite a bit
shorter than doing the one query with the or. (both fields are indexed)

In the query above, is the GenerateSndxIndex UDF called once for every
row, or is it only done once... In other words, would I be better off
with a stored procedure that returns the data... Alternatively, is there
any other algorithm that can be used to return the same soundex value
for 'Smith' , 'Smith & Smith', 'Smith's Shoe Factory', etc. without
needing the Or.

Or.. Perhaps there are other better ways of doing it... Any advice
appreciated.

Regards
Colin


######################################################################
Attention:
The information in this email and in any attachments is confidential.
If you are not the intended recipient then please do not distribute,
copy or use this information. Please notify us immediately by return
email and then delete the message from your computer.
Any views or opinions presented are solely those of the author.
######################################################################