SQL Server SOUNDEX() Function

Summary: in this tutorial, you will learn how to use the SQL Server SOUNDEX() function to evaluate the similarity between two strings.

SQL Server SOUNDEX() function overview

In SQL Server, the SOUNDEX() function accepts a string and converts it to a four-character code that represents the phonetic representation of a string.

Here’s the basic syntax of the SOUNDEX() function:

SOUNDEX(character_expression);Code language: SQL (Structured Query Language) (sql)

In this syntax:

• character_expression is the string that you want to convert to a Soundex code. The character_expression can be a constant, a column, or a variable.

The first character of the SOUNDEX code is always the first character of the character_expression. The second to fourth characters of the code are the numbers that represent the letters in the character_expression.

The function ignores the letters A, E, I, O, U, H, W, and Y if they are not the first letters of the input string.

The SOUNDEX() function will add zeros at the end of the result code if necessary to make a four-character code.

The SOUNDEX() function can be useful when you deal with data that may have variations in spelling but sound similar.

To check the similarity between SOUNDEX codes of two strings, you use the DIFFERENCE() function.

SQL Server SOUNDEX() examples

Let’s take some examples of using the SOUNDEX() function.

1) Using SQL Server SOUNDEX() function for the strings with the same sound

This example uses the SOUNDEX() function to return the SOUNDEX code of both string 'sea' and 'see':

SELECT 
    SOUNDEX('see') see_soundex, 
    SOUNDEX('sea') sea_soundex;Code language: SQL (Structured Query Language) (sql)

Here is the output:

see_soundex sea_soundex
----------- -----------
S000        S000

(1 row affected)

The SOUNDEX codes are the same because see and sea have the same sound.

2) Using the SOUNDEX() function for the strings with different sound

The following example uses the SOUNDEX() function to get the codes for the string 'coffee' and 'black' which have different sounds:

SELECT 
    SOUNDEX('coffee') see_soundex, 
    SOUNDEX('tea') sea_soundex;Code language: SQL (Structured Query Language) (sql)

The output is as follows:

see_soundex sea_soundex
----------- -----------
C100        T000

(1 row affected)

The Soundex of coffee and tea are different because they have different sounds.

Summary

• Use the SQL Server SOUNDEX() function to get the SOUNDEX code of a string.