Let’s face it, not everyone is a perfect speller all the time. Exact match queries are pretty easy to do in SQL Server. But, what if you wanted to correct their spelling or search based on a misspelled word? How is this possible? In SQL Server 2008+ you have the ability to do this with the SOUNDEX function.
The SOUNDEX function will take an input word and convert it in to a 4 character code representing the sound of the word. For example: goat -> G300.
So what does this 4 character code mean? The first character represents the first character in the string, the rest of the characters represent the rest of the characters in the string. Of course SQL Server does some magic to come up with this code. If you are interested more in the makeup of this code, see the MSDN link at the bottom of this post. Essentially, this 4 character code represents the sound of the word.
How To Use It
You can use this just like any other function in SQL Server.
SELECT SOUNDEX('dog') AS SoundOfDog
An easy way to search based on word sound would be something like this:
SELECT * FROM Animal WHERE SOUNDEX(AnimalName) = SOUNDEX('dug')
The above query would find words like “dog”, “dig”, “dag”