Hi ,

I need to identify some close match's in 2 sets off data and was thinking of using soundex and the difference function.

The difference function supposedly represents the following:-

4 - Best Match,
3 - Relative Match
2 - Fair Match
1 - Poor Match

but as a test i did the following query and its result was a rating of 4

SELECT DIFFERENCE('Ailsa',  'alk')

How can this be the "best match"?

Am I best using LIKE instead?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
Like needs exact match of string being searched meaning

SELECT * from table  where myfield like '%alk%'

needs 'alk' in the myfield column.
rather than going to SOUNDEX, I would recommend to go for Full Text Search, it has much more capacity to handle this kind of stuff. as long as LIKE concern, it is too not powerful as Full Text Search, neither is uses Index in all cases so poor performance.
Hi there,

LIKE will perform textual matching which requires that you know some letter that must exist.  Like BOL says, DIFFERENCE will classify words into 4 levels of closeness.
If you consider all the possible words in English, you have to admit ailsa and alk are quite close? Both have the "a-l" sound.  Try these

SELECT DIFFERENCE('Ailsa',  'alk')
SELECT DIFFERENCE('Ailsa',  'bilk')
SELECT DIFFERENCE('Ailsa',  'bonk')
SELECT DIFFERENCE('Ailsa',  'boni')

which result in 4,3,2,1 respectively.  DIFFERENCE can be considered as a grosser Soundex. Because soundex rates the sound in 4 parts, it can produce a far larger matrix, try

select soundex('ailsa'), soundex('alk')
select soundex('ailsa'), soundex('bilk')
select soundex('ailsa'), soundex('bonk')
select soundex('ailsa'), soundex('boni')

Hope that helps

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dwhitfield_CPWNAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.