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.

Advertisements

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.

3 thoughts on “SOUNDEX function Oracle”

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s