Optimize Sorting for Soundex Search Results
in Microsoft SQL Server
Provided by: Jim Ferguson, Project Manager
The Microsoft SQL Server Soundex function is used to find similar strings
based on a search pattern and ideal when there are small typos that result
in duplicate values. The Soundex function calculates a numerical value for a
For more information on the Soundex function in SQL Server, visit
Microsoft's SQL Server Developer Center.
It's a bit tricky to sort the results of all the records with the same
Soundex value. Sorting on the original field just puts them in alphabetical
order rather than by closeness to the desired value.
Fortunately, there's a simple way to display the exact and
partially-matching strings first in the search results. Use a sort
expression to ensure that exact and partial matches sort to the top of the
list before other matching strings.
In this example, we're showing the names with a Soundex value of "knapp'.
The display sorts all the 'knapp' values first, then those that start with 'knapp',
and finally the other records with spellings that result in the same Soundex
value as 'knapp':
where soundex(lastname) = soundex('knapp')
case when lastname like'knapp%'
The result is a list that shows the exact matches, those
that start with the original letters, and the remaining "close" ones.