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 ’
’ will be:
SELECT CustomerName FROM t_CustomerMaster WHERE CustomerName =’Chapell’
Now imagine that the user searching the database enters ‘
’ in the textbox and hits enter. He will hit enter and will not get any results. So usually, we use the
operator in the
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 ‘
’. So the result set can contain anything like Chapel, Chapele, Richap, etc.
But will this search query still work if I search for ‘
’ instead of ‘
The answer to this question is the
function in Transact SQL.
returns a four digit code for a
value passed to it. The same code is returned for all those names whose pronunciation is similar.
will return the same code.
More information on
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 ‘
’ even if you search for ‘
’. So re-constructing our search query by combining the power of
will return the same results even if the spelling Of ‘
’ is changed
WHERE (CustomerName LIKE ’%Chapell%’ OR (SOUNDEX(CustomerName) LIKE SOUNDEX(’%Chapell%’)))
The above query will return the same result set even if you use