Oracle SOUNDEX() Function

Oracle SOUNDEX() is a built-in function that returns a string representing the pronunciation of a given English string.

Soundex is a phonetic algorithm. If two words sound alike, they should have the same Soundex string.

Oracle SOUNDEX() Syntax

Here is the syntax of the Oracle SOUNDEX() function:

SOUNDEX(str)

Parameters

str

Required. It can be any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

Return Value

The Oracle SOUNDEX() function returns a string representing the pronunciation of a given English string.

If the parameter is NULL or an empty string, SOUNDEX() will return NULL.

Soundex Algorithm

The Soundex phonetic representation is defined as follows in Donald E. Knuth’s The Art of Computer Programming, Volume 3: Sorting and Searching:

  1. Retain the first letter of the string and drop all occurrences of the following letters: a, e, h, i, o, u, w, y.

  2. Assign numbers to the remaining letters (after the first letter) 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 are adjacent in the original name (before step 1), or if two or more letters with the same number are separated by h or w (but not by any other letters), then omit all but the first letter.

  4. Return the first four characters padded with 0s if needed.

Oracle SOUNDEX() Examples

Here are some examples that demonstrate the usage of the Oracle SOUNDEX() function.

Basic Usage

SELECT
    SOUNDEX('Hello'),
    SOUNDEX('World')
FROM dual;

Output:

SOUNDEX('HELLO')    SOUNDEX('WORLD')
___________________ ___________________
H400                W643

Same Pronunciation

If two words sound alike, they should have the same Soundex string.

SELECT
    SOUNDEX('Dam') "Dam",
    SOUNDEX('Damn') "Damn"
FROM dual;

Output:

Dam     Damn
_______ _______
D500    D500

In this example, “Dam” and “Damn” have the same pronunciation, so they return the same string. Let’s look at some more examples of words with the same pronunciation:

SELECT
    SOUNDEX('Too') "Too",
    SOUNDEX('Two') "Two",
    SOUNDEX('Color') "Color",
    SOUNDEX('Colour') "Colour"
FROM dual;

Output:

Too     Two     Color    Colour
_______ _______ ________ _________
T000    T000    C460     C460

NULL Parameter

If the parameter is NULL or an empty string, SOUNDEX() will return NULL.

SET NULL 'NULL';
SELECT
    SOUNDEX(NULL),
    SOUNDEX('')
FROM dual;

Output:

SOUNDEX(NULL)    SOUNDEX('')
________________ ______________
NULL             NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle SOUNDEX() is a built-in function that returns a soundex string representing the pronunciation of a given string.