Click here to Skip to main content
11,409,801 members (57,015 online)
Click here to Skip to main content

Tagged as

SOUNDEX() in Microsoft SQL 2005

, 6 Nov 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Use of soundex() function in SQL server
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

aspnet -i
Web Developer
India India
Software developer by profession, working for a service and product based organisation in India.

Career graph:
Software Programmer since 2002.
Web Developer in ASP.NET since 2004.

Interests:
I love reading the blogs and articles of technology experts. I love codeproject and stackoverflow .

I love to share knowledge and help the programmers. I appreciate if some body corrects my code or my concepts which helps me learn.

Comments and Discussions

 
SuggestionYou have a wrong query there! PinprofessionalAnkur\m/14-Jan-15 1:59 
GeneralIf I recall correctly, the soundex is dependant on the langu... PinmemberKabwla.Phone7-Nov-11 23:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150414.5 | Last Updated 6 Nov 2011
Article Copyright 2011 by aspnet -i
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid