DB2 Version 9.7 for Linux, UNIX, and Windows

SOUNDEX scalar function


>>-SOUNDEX--(--expression--)-----------------------------------><

The schema is SYSFUN.

Returns a 4-character code representing the sound of the words in the argument. The result can be used to compare with the sound of other strings.

The argument can be a character string that is either a CHAR or VARCHAR not exceeding 4000 bytes. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. The function interprets data that is passed to it as if it were ASCII characters, even if it is encoded in UTF-8.

The result of the function is CHAR(4). The result can be null; if the argument is null, the result is the null value.

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 out words with similar sounds. The comparison can be done directly or by passing the strings as arguments to the DIFFERENCE function.

Example:

Using the EMPLOYEE table, find the EMPNO and LASTNAME of the employee with a surname that sounds like 'Loucesy'.

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

This example returns the following:

EMPNO  LASTNAME
------ ---------------
000110 LUCCHESSI