Oracle PL/SQL Programming, Third Edition

Name

SOUNDEX

Synopsis

The SOUNDEX function allows you to perform string comparisons based on phonetics (the way a word sounds) as opposed to semantics (the way a word is spelled).^[12]

SOUNDEX returns a character string that is the “phonetic representation” of the argument. The specification of the SOUNDEX function is as follows:

FUNCTION SOUNDEX (string1 IN VARCHAR2) RETURN VARCHAR2

Here are some of the values SOUNDEX generated, and their variations according to the input string:

SOUNDEX ('smith') --> 'S530'
SOUNDEX ('SMYTHE') --> ''S530'
SOUNDEX ('smith smith') --> 'S532'
SOUNDEX ('smith z') --> 'S532'
SOUNDEX ('feuerstein') --> 'F623'
SOUNDEX ('feuerst') --> 'F623'

Keep the following SOUNDEX rules in mind when using this function:

• The SOUNDEX value always begins with the first letter in the input string.

• SOUNDEX uses only the first five consonants in the string to generate the return value.

• Only consonants are used to compute the numeric portion of the SOUNDEX value. Except for leading vowels, all vowels are ignored.

• SOUNDEX is not case-sensitive. Upper- and lowercase letters return the same SOUNDEX value.

The SOUNDEX function is useful for ad hoc queries, and any other kinds of searches where the exact spelling of a database value is not known or easily determined.

Warning

The SOUNDEX algorithm is English-centric and may not work well (or at all) for other languages.

^[12] Oracle Corporation uses the algorithm in Donald Knuth’s The Art of Computer Programming, Volume 3, to generate ...