|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
Functions, 114 of 177
Text description of soundex
SOUNDEX returns a character string containing the phonetic representation of
char. This function lets you compare words that are spelled differently, but sound alike in English.
The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:
- Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
- 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
- If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.
- Return the first four bytes padded with 0.
char can be of any of the datatypes
NVARCHAR2. The return value is the same datatype as char.
This function does not support
CLOB data directly. However,
CLOBs can be passed in as arguments through implicit data conversion. Please refer to "Datatype Comparison Rules" for more information.
The following example returns the employees whose last names are a phonetic representation of "Smyth":
SELECT last_name, first_name