SOUNDEX function Oracle

SOUNDEX function help you to find words matching phonetically ( By pronunciation )

Syntax :- SOUNDEX(column_name)

SOUNDEX is very useful for finding similar pronouncing names. For example say I need to find all employee sound “Daniel”. Phonetically Daniel,Danial and Daniyal are same. So below query will find all “Daniel”s

SQL> select empno,empname from emp where soundex(empname) = soundex(‘danial’);

———- ——————–
22              Daniel

SQL> select empno,empname from emp where soundex(empname) = soundex(‘daniyal’);

———- ——————–
22               Daniel

From Oracle Documentation below given algorithm for SOUNDEX

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 omit all but the first.
  4. Return the first four bytes padded with 0.

See the below query result

SQL> select soundex(‘Daniel’),soundex(‘Danial’),soundex(‘Daniyal’) from dual;

—-      —-       —-
D540   D540   D540

So all returns same value.

4 responses to “SOUNDEX function Oracle”

  1. google seo works on it..

  2. I think the admin of this website is genuinely working
    hard for his website, as here every information is quality based