The SOUNDEX function in SQL Server converts an alphanumeric string into a four-character code designed to represent the pronunciation of the string. This phonetic algorithm allows for comparisons based on sound rather than exact spelling, which is particularly useful for fuzzy matching tasks where data entry variations occur.

SOUNDEX Syntax and Mechanics

The syntax for the function is simple:

SOUNDEX(character_expression)

The function accepts a string expression and returns a varchar value. The resulting code is constructed using specific rules:

• The first character of the code is always the first character of the input string, converted to uppercase.
• Subsequent characters are digits representing the phonetic value of the remaining letters.
• Vowels (A, E, I, O, U) and the letters H, W, and Y are ignored unless they appear as the first character.
• If the generated code is shorter than four characters, it is padded with zeros.

The underlying algorithm maps consonants to digits as follows:

• 1: B, F, P, V
• 2: C, G, J, K, Q, S, X, Z
• 3: D, T
• 4: L
• 5: M, N
• 6: R

For example, the names "Robert" and "Rupert" both produce the code R163 because they sound similar. The process involves retaining the first letter, mapping the remaining consonants to numbers, dropping the mapped values for vowels, and truncating or padding the result to four characters.

SELECT SOUNDEX('Robert') AS Code1, SOUNDEX('Rupert') AS Code2;

Result:

Code1 Code2
----- -----
R163  R163

Comparing Similarity with DIFFERENCE

While SOUNDEX generates a specific code, the DIFFERENCE function evaluates the phonetic similarity between two expressions by comparing their SOUNDEX values. It returns an integer ranging from 0 to 4.

DIFFERENCE(character_expression, character_expression)

The return value indicates the degree of similarity:

• 4: The highest similarity (identical or nearly identical pronunciation).
• 0: No phonetic similarity.

Consider the following example comparing "Catherine" and "Kathryn":

SELECT 
  SOUNDEX('Catherine') AS CodeA, 
  SOUNDEX('Kathryn') AS CodeB,
  DIFFERENCE('Catherine', 'Kathryn') AS SimilityScore;

Result:

CodeA CodeB SimilityScore
----- ----- -------------
C350  K350  3

Although the spellings and the first characters differ significantly, the phonetic structures are close enough to yield a high similarity score. Conversely, comparing two distinctly different words results in a lower score:

SELECT DIFFERENCE('Physics', 'History') AS SimilityScore;

Result:

SimilityScore
-------------
2

Database Compatibility Notes

The behavior of SOUNDEX depends on the database compatibility level. Databases set to compatibility level 110 (SQL Server 2012) or higher utilize a more complete set of rules than earlier versions. When upgrading an older database to this level, objects relying on SOUNDEX may require maintenance:

• Heaps with persisted computed columns must be rebuilt using ALTER TABLE [TableName] REBUILD.
• Indexes on persisted computed columns require rebuilding via ALTER INDEX ALL ON [ObjectName] REBUILD.
• Check constraints defined with SOUNDEX should be re-enabled and validated using ALTER TABLE [TableName] WITH CHECK CHECK CONSTRAINT ALL.