T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL SOUNDEX() Function



The T-SQL (Transact-SQL) SOUNDEX() function returns a soundex string from a given string. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long.

This function converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken in English. The first character of the code is the first character of str, converted to upper case. The second through fourth characters of the code are numbers that represent the letters in the str. The letters A, E, I, O, U, H, W, and Y are ignored unless they are the first letter of the str. Zeroes are added at the end if necessary to produce a four-character code.

SOUNDEX codes from different strings can be compared to see how similar the strings sound when spoken. The DIFFERENCE() function performs a SOUNDEX on two strings, and returns an integer that represents how similar the SOUNDEX codes are for those strings.

Syntax

SOUNDEX(str)

Parameters

str Required. Specify a string whose soundex string is to be retrieved.

Return Value

Returns the soundex string from a given string.

Example 1:

The example below shows the usage of SOUNDEX() function.

SELECT SOUNDEX('Hello');
Result: 'H400'

SELECT SOUNDEX('Principal');
Result: 'P652'

SELECT SOUNDEX('Principle');
Result: 'P652'

Example 2:

Consider a database table called Sample with the following records:

DataWords
Data1Here
Data2Heir
Data3Smith
Data4Smythe
Data5To
Data6Too
Data7Two

To get the soundex string of all records of Words column, the following query can be used:

SELECT *, SOUNDEX(Words) AS SOUNDEX_Value FROM Sample;

This will produce a result similar to:

DataWordsSOUNDEX_Value
Data1HereH600
Data2HeirH600
Data3SmithS530
Data4SmytheS530
Data5ToT000
Data6TooT000
Data7TwoT000

❮ T-SQL Functions