Microsoft SQL Server ResourcesOptimize Sorting for Soundex Search Results in Microsoft SQL Server

Provided by: Jim Ferguson, Project Manager

Soundex Function

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 string.

For more information on the Soundex function in SQL Server, visit Microsoft's SQL Server Developer Center.

Problem

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.

Solution

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':

select lastname 
from Customers 
where soundex(lastname) = soundex('knapp')
order by
case when lastname like'knapp%'
then 0
else 1
end,
lastname

The result is a list that shows the exact matches, those that start with the original letters, and the remaining "close" ones.

Strategic Overview

SQL Server Express

SQL Azure Cloud

What it Means to Information Workers

Link Microsoft Access to SQL Azure Databases

Deploy MS Access DBs linked to SQL Azure

Eliminate SQL Azure Security Holes

Monitor SQL Server Usage and DTU Limits on Azure

Convet Azure SQL Server to Elastic Pools

SQL Server Tips

Database Users and Permissions

Database Backup and Restore on Network

AM/PM Date Time Format

Get Date Portion

Set NOCOUNT ON

Running Total

Videos

Migrate Your Data Tier to SQL Server: Strategies for Survival

Microsoft Access Database and Migration Challenges

Are we there yet? Successfully navigating the bumpy road from Access to SQL Server

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Services

Microsoft SQL Server Consulting Services

Visual Studio .NET programmers for web development

General Microsoft Access consulting services

Additional Resources

Microsoft Access Help

Microsoft Access Developer Help Center

MS Access Developer Programming

More Access and SQL Tips

Technical Papers

Microsoft Access Tools

Connect with Us

 

Free Product Catalog from FMS