Encodes a string value into a four-character string.
SOUNDEX works for characters in the English alphabet (A-Z). It uses the first character of the input string as the first character in the return value and encodes the remaining three unique consonants as numbers.
SOUNDEX encodes characters according to the following list of rules:
•Uses the first character in string as the first character in the return value and encodes it in uppercase. For example, both SOUNDEX(‘John’) and SOUNDEX(‘john’) return ‘J500’.
•Encodes the first three unique consonants following the first character in string and ignores the rest. For example, both SOUNDEX(‘JohnRB’) and SOUNDEX(‘JohnRBCD’) return ‘J561’.
• Assigns a single code to consonants that sound alike.
The following table lists SOUNDEX encoding guidelines for consonants:
Code
Consonant
1
B, P, F, V
2
C, S, G, J, K, Q, X, Z
3
D, T
4
L
5
M, N
6
R
•Skips the characters A, E, I, O, U, H, and W unless one of them is the first character in string. For example, SOUNDEX(‘A123’) returns ‘A000’ and SOUNDEX(‘MAeiouhwC’) returns ‘M000’.
•If string produces fewer than four characters, SOUNDEX pads the resulting string with zeroes. For example, SOUNDEX(‘J’) returns ‘J000’.
•If string contains a set of consecutive consonants that use the same code listed in the table above, SOUNDEX encodes the first occurrence and skips the remaining occurrences in the set. For example, SOUNDEX(‘AbbpdMN’) returns ‘A135’.
•Skips numbers in string. For example, both SOUNDEX(‘Joh12n’) and SOUNDEX(‘1John’) return ‘J500’.
•Returns NULL if string is NULL or if all the characters in string are not letters of the English alphabet.
Syntax
SOUNDEX( string )
Argument
Required/
Optional
Description
string
Required
Character string. Passes the string value you want to encode. You can enter any valid expression.
Return Value
String.
NULL if one of the following conditions is true:
•If value passed to the function is NULL.
•No character in string is a letter of the English alphabet.
•string is empty.
Example
The following expression encodes the values in the EMPLOYEE_NAME column: