The following SELECT statement returns the result that follows.
SELECT SOUNDEX ('ashcraft');
Soundex('ashcraft')
-------------------
a261
The surname "ashcraft" initially evaluates to "a2h2613", but the following SOUNDEX rules convert the result to a261.
- SOUNDEX drops "h" because "h" in the third position. SOUNDEX drops all occurrences of the following characters in any position other than the first:
A, E, I, O, U, Y, H, W
- SOUNDEX drops "2" because "2" is the second occurrence of one of the following characters:
C, G, J, K, Q, S X, Z
If two or more characters with the same code are adjacent in the original name, or adjacent except for any intervening H or W, Soundex omits all but the code for the first occurrence of the character in the returned code.
- "3" is dropped because SOUNDEX drops the rightmost digits if character_string evaluates to more than three digits following the initial simple Latin character.
The following two examples use the following table data:
SELECT family_name FROM family; family_name ----------- John Joan Joey joanne michael Bob
Here are the results of the SOUNDEX function on the data in the family_name column:
SELECT SOUNDEX(TRIM(family.family_name));
Soundex(TRIM(BOTH FROM family_name)) ------------------------------------ J500 J500 B100 J000 m240 j500
Find all family names in Family that sound like "Joan".
SELECT family_name
FROM family
WHERE SOUNDEX(TRIM(family.family_name)) = SOUNDEX('Joan');
The following SOUNDEX examples are not valid for the reasons given in the table.
| Statement | Why the Statement is Not Valid |
|---|---|
| SELECT SOUNDEX(12345); | 12345 is a numeric string, not a character string. |
| SELECT SOUNDEX('ábç'); | The characters á and ç are not simple Latin characters. |