orabase: Soundex Function

Given a String, the Soundex function returns a character string which is its Phonetic Representation. This is useful for performing "Sounds Like" comparisons.(See the example below)

The Soundex algorithm is 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.

The soundex function uses only the first 5 consonants to determine the NUMERIC portion of the return value, except if the first letter of string1 is a vowel. The soundex function is not case-sensitive. ie. both uppercase and lowercase characters will generate the same soundex return value.



Syntax:
SOUNDEX()

Example:
CREATE TABLE test123
 (name VARCHAR2(15));

INSERT INTO test123 VALUES ('Smith');
INSERT INTO test123 VALUES ('Smyth');
INSERT INTO test123 VALUES ('Smythe');
INSERT INTO test123 VALUES ('Smither');
INSERT INTO test123 VALUES ('Smidt');
INSERT INTO test123 VALUES ('Smick');
INSERT INTO test123 VALUES ('Smiff');
COMMIT;

SELECT * FROM test123;

SELECT SOUNDEX(NAME), NAME
FROM test123
WHERE SOUNDEX(name) = SOUNDEX('SMITH');

Source:
http://www.psoug.org/reference/string_func.html
http://www.techonthenet.com/oracle/functions/soundex.php