Oracle SOUNDEX Function with Examples

The Oracle SOUNDEX function is one of the most interesting functions I’ve used in Oracle. Learn what it is and how it works in this article.

Purpose of the Oracle SOUNDEX Function

The Oracle SOUNDEX function allows you to check what a value sounds like. It returns a value that represents the phonetic value of a string.

What does that mean?

Well, you know that the letter “a” in “apple” sounds different to the letter “a” in “army”? The SOUNDEX function can work that out. It finds out the phonetic value of the string you give it.

Phonetic means that it looks the way that it sounds. Like the phonetic alphabet that you might have seen in TV shows or movies.

 

Syntax

The syntax of the Oracle SOUNDEX function is:

SOUNDEX ( string )

 

Parameters

The parameters of the STRING function are:

• string (mandatory): This is the value that will be converted to a phonetic value and then returned.

 

How Does The SOUNDEX Algorithm Work?

The SOUNDEX function works like this:

1. The function looks at the first letter of the string.
2. Then, it removes all other occurrences of the letters a, e, h, i, o, u, w, y.
3. It then assigns a number to the remaining letters:
   • b, f, p, w = 1
   • c, g, j, k, q, s, x, z = 2
   • d, t = 3
   • l = 4
   • m, n = 5
   • r = 6
4. If the function finds there are two or more letters with the same number in the original string, or adjacent except for any intervening h or ow, then remove all but the first.
5. Finally, return the first four bytes, padded with 0.

Basically, it uses the first 4 or 5 characters of the string to determine how a word sounds, and returns an alphanumeric value to represent it.

See the Examples section for more information on what the output looks like.

 

How Can I Find Values That Sound Like Another Value?

A common use of the SOUNDEX function is to find values that sound like another value.

For example, in your database, you have a surname of “Smith”. However, you also have a surname of “Smythe”.

How do you find all records where the surname sounds like Smith, which would include Smith, Smythe, and any other variations?

This is where you would use the SOUNDEX function.

To do this, your query would include the SOUNDEX function in the WHERE clause:

SELECT first_name, last_name
FROM person
WHERE SOUNDEX(last_name) = SOUNDEX('Smith');

The important thing to remember (I’ve forgotten this many times and wondered why my result set was empty) is that you must use SOUNDEX on both sides of the WHERE clause to match the values.

The SOUNDEX function returns an alphanumeric value that represents how a letter sounds (e.g. A123). If you match this to a string value, then no result will be found.

For example, if you write this:

WHERE last_name = SOUNDEX('Smith)

You’re basically saying WHERE last_name = S123, which doesn’t match.

So, remember to put the SOUNDEX on both sides of the WHERE clause.

 

Examples of the SOUNDEX Function

Here are some examples of the SOUNDEX function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example is a basic usage of the SOUNDEX function.

SELECT SOUNDEX('Complete');

Result:

C514

 

Example 2

This example uses the lower case version of “complete”, which has the same return value as above.

SELECT SOUNDEX('complete');

Result:

C514

 

Example 3

This example uses SOUNDEX on two similar sounding words – coughing and coffin.

SELECT SOUNDEX('Canne'),
SOUNDEX('Can')
FROM dual;

Result:

SOUNDEX(‘CANNE’)	SOUNDEX(‘CAN’)
C500	C500

 

Example 4

SELECT first_name, last_name
FROM student
WHERE SOUNDEX(last_name) = SOUNDEX('Smyth');

Result:

FIRST_NAME	LAST_NAME
John	Smith
Sarah	Smyth
David	Smythe

 

Similar Functions

There aren’t really any similar functions to the SOUNDEX function.

The SUBSTR function can be used to check if the first few characters between two strings match, but it only works on an exact match and not what each word sounds like.

There is an alternative algorithm to SOUNDEX, called metaphone, but this needs to be implemented using a PL/SQL function. I’ll write that up in a separate article.

You can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!