SOUNDEX() in Microsoft SQL 2005

Many times, we need to search the database for e.g. to search the database for Customer names.

So the general Query to search a name ’Chapell’ will be:

SELECT CustomerName FROM t_CustomerMaster WHERE CustomerName =’Chapell’

Now imagine that the user searching the database enters ‘Chapple’ in the textbox and hits enter. He will hit enter and will not get any results. So usually, we use the LIKE operator in the WHERE clause and search for a substring.

SELECT CustomerName FROM t_CustomerMaster WHERE CustomerName LIKE ’%Chap%’

So this one will work and return all the names that contain the substring ‘Chap’. So the result set can contain anything like Chapel, Chapele, Richap, etc.

But will this search query still work if I search for ‘Chipell’ instead of ‘Chapell’?

The answer to this question is the SOUNDEX() function in Transact SQL.

The SOUNDEX() returns a four digit code for a varchar value passed to it. The same code is returned for all those names whose pronunciation is similar.

For example:

SOUNDEX(‘Smith’), SOUNDEX(‘Smythe’),SOUNDEX(‘Smithe’)

will return the same code.

More information on SOUNDEX() can be found at http://msdn.microsoft.com/en-us/library/aa259235(SQL.80).aspx.

As a result, the search query can be tuned to return the result set for ‘Chapell’ even if you search for ‘Chipell’. So re-constructing our search query by combining the power of LIKE and SOUNDEX() will return the same results even if the spelling Of ‘Chapell’ is changed

SELECT CustomerName
FROM t_CustomerMaster
WHERE (CustomerName LIKE ’%Chapell%’ OR (SOUNDEX(CustomerName) LIKE SOUNDEX(’%Chapell%’)))

The above query will return the same result set even if you use Chap, Chapel, Chapelle, Chipell instead of Chapell.