SOUNDEX scalar function

The SOUNDEX function returns a 4-character code that represents the sound of the words in the argument. The result can be compared to the results of the SOUNDEX function of other strings.

The schema is SYSIBM.

expression: An expression that must return a value of any built-in numeric, character, or graphic string data type that is not a LOB. A numeric, mixed character, or graphic string value is cast to a Unicode SBCS character string before the function is evaluated. For more information about converting numeric data to a character string, see VARCHAR scalar function. For more information about converting mixed or graphic strings to Unicode SBCS, see CAST specification.

The data type of the result is CHAR(4).

The result can be null; if the argument is null, the result is the null value.

The CCSID of the result is the Unicode SBCS CCSID.

The SOUNDEX function is useful for finding strings for which the sound is known but the precise spelling is not. It makes assumptions about the way that letters and combinations of letters sound that can help to search for words with similar sounds. The comparison of words can be done directly or by passing the strings as arguments to the DIFFERENCE function. For more information, see DIFFERENCE scalar function.

Example 1: Use the SOUNDEX function to find a row where the sound of the LASTNAME value closely matches the phonetic spelling of 'Loucesy':

   SELECT EMPNO, LASTNAME
      FROM DSN910.EMPLOYEE
      WHERE SOUNDEX(LASTNAME) = SOUNDEX('Loucesy');

This example returns the following row:

   000110 LUCCHESSI;