Home > SQL Server, T-SQL > SOUNDEX Function – SQL Server

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

“Soundex is a phonetic algorithm for indexing names by sound.” – Wikipedia

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
4                 L
5                 M, N
6                 R

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 )

Example:

SELECT SOUNDEX(‘SQL SERVER’);

Experiment 1:

SELECT SOUNDEX(‘here’), SOUNDEX(‘hear’)

Output

H600 H600

Experiment 2:

SET LANGUAGE french
SELECT SOUNDEX(‘langue’);
SET LANGUAGE english
SELECT SOUNDEX(‘language’);

Output
L520
L522

Experiment 3:

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’)

Output

0000 0000

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.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: