• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:


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?

1 Solution
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
dwhitfield_CPWNAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now