Quick Search:
 The Oracle PL/SQL SOUNDEX Function      [Return To Index] Jump to:  


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:


Example Usage:

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


---- ---- ----
T500 T500 T500

Related Links:

Related Code Snippets:
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org