SOUNDEX Function – SQL Server
SOUNDEX() function returns a four-character (SOUNDEX) code for a character expression. The result data type is as varchar. The soundex code can be use to find the similarities between two string in the real world scenario. SOUNDEX function is collation sensitive. So, it generates different soundex code based on the database collation settings. But, you can change the collation setting using set language command and get different output in run-time.
If you have upgraded the database compatibility level to 110 then you may need to rebuild the indexes, heaps, or CHECK constraints that uses the SOUNDEX function.
About SOUNDEX code
Every soundex code consists of a letter and three numbers, for instance W-252. The letter is always the first letter of the given string. The rest of three digit number is generated base of the following table.
Number Represents the Letters
1 B, F, P, V
2 C, G, J, K, Q, S, X, Z
3 D, T
5 M, N
Each set of character(s) are assigned with a number. But, letters such as (A, E, I, O, U, H, W, and Y) will be discarded unless it is the starting letter in the string.
Learn more about soundex code rules here.
You can also convert soundex code online in this website.
How to use this function?
SOUNDEX ( character_expression )
SELECT SOUNDEX(‘SQL SERVER’);
SELECT SOUNDEX(‘here’), SOUNDEX(‘hear’)
SET LANGUAGE french
SET LANGUAGE english
The output of the following query is 0000. Because, soundex function calculates soundex code from alphanumeric value and it cannot generate code for Numeric value.
SELECT SOUNDEX(‘123’), SOUNDEX(‘456’)
Finally, Soundex function is useful in many real world application in which to find similarities between two strings for decision-making. An example would be searching for name in the database without knowing the correct spelling of it.
Thanks for reading.