Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL SOUNDEX Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: SOUNDEX

Definition:
In Oracle PL/SQL, SOUNDEX is a built in function used to compare data items with their audiable or spoken values. It is a phonetic algorithm for indexing names by sound, as pronounced in English. For example, REIN, REIGN, and RAIN are all spelled differently but sound the same when spoken aloud. These "sound-alike" words are referred to as "homophones" or "heterographs". The National Archives and Records Administration (NARA) maintains the current rule set for the official implementation of Soundex used by the U.S. Government.

Oracle follows the algorithm shown below to implement the SOUNDEX function:

  1. Keep 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 letter) 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 any intervening h's and w's, then remove all but the first.

  4. Return the first four bytes padded with 0.
Example Syntax:

SOUNDEX(string)


Example Usage:

The SQL example below shows the phonetic representation of REIN, REIGN, and RAIN.

SQL> SELECT SOUNDEX('REIN'),SOUNDEX('REIGN'),SOUNDEX('RAIN') FROM DUAL;

SOUN SOUN SOUN
---- ---- ----
T500 T500 T500



Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 295 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?