By: Daniel Calbimonte
The SOUNDEX function is used to measure the similarity of strings. The function returns 4 characters: 1 letter and 3 numbers. This function classifies the string and uses a code to compare the sound of a string in a similar way to English. It can be used as a simplistic way of comparing two strings to see how similar they may sound.
Syntax
SOUNDEX(expression)
Parameters
- expression - this is the character string or expression to evaluate the sound.
Letter to Number Conversion for SOUNDEX
Here is a list of the number values that are returned based on the corresponding letter returned by SOUNDEX. We will walk through some examples below.
| Number | Letter |
|---|---|
| 1 | b, f, p, v |
| 2 | c, g, j, k, q, s, x, z |
| 3 | d, t |
| 4 | l |
| 5 | m, n |
| 6 | r |
Letters SOUNDEX Ignores
- The following letters are ignored: a, e, I, o, u, h, w and y.
Simple SOUNDEX Example
The following example will show the SOUNDEX of the word JOHN.
SELECT SOUNDEX('JOHN') as string
J500 means that the first letter is J from JOHN. Then the O and H are ignored (according to rules above) and finally the N is a 5 as shown in the table above. As we do not have more characters, the rest is ignored and 0 is returned for positions three and four.
SOUNDEX Example with Longer Word
In the next example we have the word "Consideration".
SELECT SOUNDEX('Consideration') as string
The value returned is C523 as shown below.
- C is the first letter
- o is ignored
- n is 5
- s is 2
- i is ignored
- d is 3
- we now have the 4 character SOUNDEX code, so the rest of the characters are ignored.
Using DIFFERENCE and SOUNDEX
SOUNDEX is closely related to the DIFFERENCE function. The DIFFERENCE function compares the output of SOUNDEX and returns a number of letters and numbers that are common. The following example illustrates this.
SELECT DIFFERENCE('JANE','JAKE') as difference, SOUNDEX('JANE') as string1, SOUNDEX('JAKE') as string2
JANE and JAKE sound similar. The values returned are J500 and J200. Note that they have 3 values in common and therefore the DIFFERENCE shows a value of 3.
