How does the Soundex function work in SQL Server?

Profile picture for Naveen Suman
Naveen Suman

Here's an example of Soundex code in SQL:

<!-- language : lang-sql --> 
SELECT SOUNDEX('Smith'), SOUNDEX('Smythe');
----- ----- 
S530  S530

How does 'Smith' become S530?

In this example, the first digit is S because that's the first character in the input expression, but how are the remaining three digits are calculated?

What is Soundex?

Soundex is:

a phonetic algorithm for indexing names by sound, as pronounced in English; first developed by Robert C. Russell and Margaret King Odell in 1918

How does it Work?

There are several implementations of Soundex, but most implement the following steps:

  1. Retain the first letter of the name and drop all other occurrences of vowels and h,w:
    |a, e, i, o, u, y, h, w | → "" |
  2. Replace consonants with numbers as follows (after the first letter):
    | b, f, p, v | → 1 |
    | c, g, j, k, q, s, x, z | → 2 |
    | d, t | → 3 |
    | l | → 4 |
    | m, n | → 5 |
    | r | → 6 |
  3. Replace identical adjacent numbers with a single value (if they were next to each other prior to step 1):
    | M33 | → M3 |
  4. Cut or Pad with zeros or cut to produce a 4 digit result:
    | M3 | → M300 |
    | M34123 | → M341 |

Here's an interactive demo in jsFiddle:

jsFiddle Demo

And here's a demo in SQL using SQL Fiddle

In SQL Server, SOUNDEX is often used in conjunction with DIFFERENCE, which is used to score how many of the resulting digits are identical (just like the game mastermind<sup>†</sup>), with higher numbers matching most closely.

Mastermind

What are the Alternatives?

It's important to understand the limitations and criticisms of soundex and where people have tried to improve it, notably only being rooted in English pronunciation and also discards a lot of data, resulting in more false positives.

Both Metaphone & Double Metaphone still focus on English pronunciations, but add much more granularity to the nuances of speech in Enlgish (ie. PH → F)

Phil Factor wrote a Metaphone Function in SQL with the source on github

Soundex is most commonly used on identifying similar names, and it'll have a really hard time finding any similar nicknames (i.e. Robert → Rob or Bob). Per this question on a Database of common name aliases / nicknames of people, you could incorporate a lookup against similar nicknames as well in your matching process.

Here are a couple free lists of common nicknames:

Further Reading: