SOUNDEX

SOUNDEX is an SQL function that returns a character string containing the phonetic representation of another string.

Conversion rules

The following rules are applied when calculating the SOUNDEX for a string:

• Keep 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 any intervening h's and w's, then remove all but the first.
• Return the first four bytes padded with 0.

Examples

Create test table:

CREATE TABLE t1 (name VARCHAR2(30));
INSERT INTO t1 VALUES ('Ann');
INSERT INTO t1 VALUES ('Anne');
INSERT INTO t1 VALUES ('Ansie');

Show conversions:

SQL> SELECT name, SOUNDEX(name) FROM t1;
NAME                           SOUN
------------------------------ ----
Ann                            A500
Anne                           A500
Ansie                          A520

Search for a names that sounds like "Ann":

SQL> SELECT * FROM t1 WHERE SOUNDEX(name) = SOUNDEX('ann');
NAME
------------------------------
Ann
Anne