Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E26088-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

SOUNDEX

Syntax

Description of soundex.gif follows
Description of the illustration soundex.gif

Purpose

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:

  1. Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.

  2. 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
    
  3. 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 retain the first letter and omit rest of all the adjacent letters with same number.

  4. Return the first four bytes padded with 0.

char can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is the same data type as char.

This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.

See Also:

"Data Type Comparison Rules" for more information.

Examples

The following example returns the employees whose last names are a phonetic representation of "Smyth":

SELECT last_name, first_name
     FROM hr.employees
     WHERE SOUNDEX(last_name)
         = SOUNDEX('SMYTHE')
     ORDER BY last_name, first_name;

LAST_NAME  FIRST_NAME
---------- ----------
Smith      Lindsey
Smith      William