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 his 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 pronounciation is similar.

For example SOUNDEX(‘Smith’), SOUNDEX(‘Smythe’),SOUNDEX(‘Smithe’) will return the same code

More information on SOUNDEX() can be found here 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

Advertisements