Click here to Skip to main content
12,894,708 members (58,378 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


2 bookmarked
Posted 4 Nov 2011

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

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.


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


About the Author

Balaji Birajdar (India)
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.

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.

You may also be interested in...

Comments and Discussions

SuggestionYou have a wrong query there! Pin
Ankur\m/14-Jan-15 0:59
professionalAnkur\m/14-Jan-15 0:59 
GeneralIf I recall correctly, the soundex is dependant on the langu... Pin
Kabwla.Phone7-Nov-11 22:45
memberKabwla.Phone7-Nov-11 22:45 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170424.1 | Last Updated 6 Nov 2011
Article Copyright 2011 by Balaji Birajdar (India)
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid