Searching thru millions of rows for records that "sound like"...

[fousek]

Hello all. I have a SQL Server 2000 db that has a table with ~10 million rows with a column LastName. Is there a way to perform a search on that column for a last name that "sounds like" my search criteria?

For example, if I search for the last name 'Meyers', I would like for it to return Meyers, Myers, Miers, etc. I'm trying the "soundex" function, but it returns too many rows. It's algorithm is not very restrictive, I don't believe.

Has anyone had to tackle this problem? I'm not married to the "soundex" function idea, so I am open to any suggestions.

Thanks in advance!

APF

 

[tlbroadbent]

Have considered installing English Query?

http://msdn.microsoft.com/library/en-us/eqbol/eqwelcme_4ilq.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[fousek]

Terry - thanks for your response. Yes, I looked at English Query and concluded that it does not handle a "sounds like" situation. Are you familiar enough with the product to know that it does? My conclusion was based on about an hour of research, so I could be wrong!

If it is smart enough to handle "What are all of the last names that sound like Meyers?", I would imagine that the underlying SQL that it uses would include a 'soundex'.

APF

 

[tlbroadbent]

I don't use English Query myself. The documentation states you can create "Sounds Like" queries. I don't know if these queries would be any better than T-SQL using SOUNDEX.

SQL BOL: Sounds Like
Find rows with fields containing words that have the same sound. For example, "where is the restaurant named bentow" would find The Bento Box, Bento Express, and Tachibana Bento.

Reference:

http://msdn.microsoft.com/library/en-us/eqbol/equi_6qlj.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[sguslan]

If you're using sql2000 look at writing your own soundex and make it restrictive. I've done that many years back for our office because we needed a restrictive form of soundex.
I did it in C though.

I serched Google.com for 'soundex code':

http://ancestor-search.com/soundex.html

http://physics.nist.gov/cuu/Reference/soundex.html

Good luck