Oracle SOUNDEX

Summary: in this tutorial, you will learn how to use the Oracle SOUNDEX() function to return a string that contains the phonetic representation of a string.

Introduction to Oracle SOUNDEX() function

The SOUNDEX() function returns a string that contains the phonetic representation of a string.

The following illustrates the syntax of the SOUNDEX() function:

SOUNDEX(expression)
Code language: SQL (Structured Query Language) (sql)

In this syntax, the expression is a literal string or an expression that evaluates to a string.

The SOUNDEX() function will return a string, which consists of four characters, that represents the phonetic representation of the expression.

The SOUNDEX() function is useful for comparing words that sound alike but are spelled differently in English.

Oracle SOUNDEX() function examples

Let’s take some examples of using the SOUNDEX() function.

1) Basic Oracle SOUNDEX() example

This example uses the SOUNDEX() function to return the Soundex of the word 'sea' and 'see'.

SELECT 
    SOUNDEX('see') see, 
    SOUNDEX('sea') sea 
FROM 
  dual;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

SEE  SEA
---- ----
S000 S000
Code language: SQL (Structured Query Language) (sql)

Because both words sound the same, they should receive the same Soundex value.

2) Using Oracle SOUNDEX() function with table data example

This example uses the SOUNDEX() function to find contacts whose last names sound like 'bull':

SELECT 
    first_name, 
    last_name 
FROM 
    contacts 
WHERE 
    SOUNDEX(last_name) = SOUNDEX('bull') 
ORDER BY 
    last_name;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this tutorial, you have learned how to use the Oracle SOUNDEX() function to compare if words are sound alike, but spelled differently in English.

Was this tutorial helpful?