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

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.

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated. View all posts by sqlandplsql