Oracle SOUNDEX() Function

The Oracle (PL/SQL) SOUNDEX() function returns a soundex string from a given string. Two strings that sound almost the same should have identical soundex strings.

The soundex string 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.

The str argument can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is the same datatype as the str argument.

Syntax

SOUNDEX(str)

Parameters

Return Value

Returns the soundex string from a given string.

Example 1:

The example below shows the usage of SOUNDEX() function.

SOUNDEX('Hello')
Result: 'H400'

SOUNDEX('Principal')
Result: 'P652'

SOUNDEX('Principle')
Result: 'P652'

Example 2:

Consider a database table called Sample with the following records:

To get the soundex string of all records of Words column, the following query can be used:

SELECT Sample.*, 
SOUNDEX(Words) AS SOUNDEX_Value 
FROM Sample;

This will produce a result similar to: