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’);

EMPNO    EMPNAME
———- ——————–
22              Daniel

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

EMPNO      EMPNAME
———- ——————–
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;

SOUN  SOUN  SOUN
—-      —-       —-
D540   D540   D540

So all returns same value.