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’);
SQL> select empno,empname from emp where soundex(empname) = soundex(‘daniyal’);
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:
- Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
- 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
- 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.
- 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.