fuzzy search

Is there a built in capability in Sql server 2005 to do a search which can handle spelling errors. for eg.

We are doing a search for "hanovr" and our database contains "hanover" . In cases when there is a spelling error searching using LIKE,CONTAINS,FREETEXT are not giving me the results. Is there an out of the box solution for this problem.

Please Advice.

Oracle has a function, Soundex if I remember right, that worked off the auditory equivalent value.

I don't remember if sql server has something like that or not.

|||

Duh. Just looked it up and there is one. Check it out!

|||

there is also a version of soundex in sql server. check it here:http://msdn2.microsoft.com/en-us/library/aa259235(sql.80).aspx

|||

There are actually 2 functions in SQL using which you can check whether 2 values are matching or not. SOUNDEX and DIFFERENCE. These functions actually ignore the vowels ( a,e,i,o,u ).

The SOUNDEX function returns a four-character code to evaluate the similarity of two strings.

The DIFFERENCE function returns the difference ( ranging from 0 to 4, 0 is the highest possible difference and 4 is the least possible difference ) between the SOUNDEX values of two character expressions. Run below queries.

select soundex ('hanovr' ) , soundex ('hanover' ) , difference ('hanovr' ,'hanover' )select soundex ('hanovr' ) , soundex ('hanovr' ) , difference ('hanovr' ,'hanovr' )

I've used them previously with not much luck. You can if possible go with some .net libraries to check spelling mistakes.

Hope this will help.

|||

I was looking for something similar - a search mechanism that deals with misspellings. Came acrosshttp://shuffletext.com/highlight, which is a little misspelling search component in beta.

Figured I'd jump on the thread, as it works pretty good. No problems integrating, but there's a couple of annoying things: it doesn't hand you back any info on how good the match was, and sometimes the first result isn't always the best result.

sql