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.
