Jun 5, 2006

Auto suggest words - nearest match with soundex

What I am trying to acheive here is to find out the the word in a dictionary table that is the closest match to the word entered by the user. I will be using the "difference" function, that actually gives the difference between the soundex values of the two words compared (More info will be there in BOL)

Lets say we have a dictionary table.

create table dictionary (words varchar(100))

And I insert a few sample words

insert into dictionary values('dictionary')
insert into dictionary values('pictionary')
insert into dictionary values('dictator')
insert into dictionary values('machinery')
insert into dictionary values('spectator')

The following scripts will get me the nearly exact match of the misspelt words.

declare @word varchar(20)

set @word = 'dictonari'
select * from dictionary where difference(words,@word) = 4

set @word = 'maziner'
select * from dictionary where difference(words,@word) = 4

If you need the nearest best match for a misspelt word, then may be you can try this..

declare @word varchar(20)
set @word = 'bitonarw'
select top 1 words from dictionary
order by difference(words,@word) desc

1 comments:

rajeshkannan said...

Really great, which i was looking for.Thannks for posting it

Post a Comment