SOUNDEX

The SOUNDEX function determines a phonetic signature for a string and allows comparisons of strings based on phonetic similarity. SOUNDEX lets you compare words that are spelled differently, but sound alike in English.

SQL syntax

SOUNDEX (InputString)

Parameters

SOUNDEX has the parameters:

Parameter Description

InputString

Valid types are CHAR, VARCHAR2, NCHAR and NVARCHAR2 with both ORA and TT variants and CLOB and NCLOB. If the data type is CLOB or NCLOB, TimesTen performs implicit conversion before returning the result.

Description

  • Converts an alpha-numeric string into a 4 character code, beginning with the first letter encountered in the string, followed by three numbers.

  • The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:

    1. Retain the first letter of the string and drop all other occurrences of the following letters: A, E, I, O, U. The treatment of the letters is case insensitive.

    2. Drop all occurrences of H, W, and Y.

    3. Assign numbers to the remaining letters (after the first) as follows:

      B, F, P, V = 1
C, G, J, K, Q, S, X, Z = 2
D, T = 3
L = 4
M, N = 5
R = 6

    4. If two or more letters with the same number were adjacent in the original name (before step 1), omit all but the first.

    5. Return the first four characters of the result (padded with '0' if the result has less than four characters).

  • The function returns NULL if no soundex code could be generated for the InputString. For example, NULL is returned when the InputString contains no English letters.

  • The input to output type mapping is:

    Input Type Output Type

    VARCHAR2(x), CHAR, CLOB

    VARCHAR2(4)

    TT_CHAR(x), TT_VARCHAR(x)

    TT_VARCHAR(4)

    NVARCHAR2(x), NCHAR(x), NCLOB

    NVARCHAR2(4)

    TT_NCHAR(x), TT_NVARCHAR(x)

    TT_NVARCHAR(4)

Examples

Use SOUNDEX function to return the phonetic signature for employees with last name equal to 'Taylor'. 

Command> SELECT last_name, first_name, SOUNDEX (last_name)
         FROM employees where last_name = 'Taylor';
< Taylor, Jonathon, T460 >
< Taylor, Winston, T460 >
2 rows found.

Invoke the function again to return the phonetic signature for the string 'Tailor'. Invoke the function a third time to return the last name and first name of each employee whose last name is phonetically similar to the string 'Tailor'.

Command> SELECT SOUNDEX ('Tailor') FROM dual;
< T460 >
1 row found.

Command> SELECT last_name, first_name FROM employees WHERE SOUNDEX (last_name) = 
           SOUNDEX ('Tailor');
< Taylor, Jonathon >
< Taylor, Winston >
2 rows found.