SOUNDEX() Function in Oracle

In Oracle, the SOUNDEX() function returns a character string containing the phonetic representation of its argument. This is known as the Soundex string.

Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English. If two words sound the same, they should have the same Soundex string. If two words sound similar, but not exactly the same, their Soundex string might look similar but not exactly the same.

The function accepts one argument: the string for which to return the Soundex string from.

Syntax

The syntax goes like this:

SOUNDEX(char)

Where char is the string for which to return the Soundex string from. It can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is the same data type as the argument.

Example

Here’s a basic example:

SELECT SOUNDEX('Bat')
FROM DUAL;

Result:

B300

Here’s another example that compares the Soundex string returned from similar, but different, sounding words:

SELECT 
    SOUNDEX('Bat'),
    SOUNDEX('Cat'),
    SOUNDEX('Cap')
FROM DUAL;

Result:

   SOUNDEX('BAT')    SOUNDEX('CAT')    SOUNDEX('CAP') 
_________________ _________________ _________________ 
B300              C300              C100

And here’s one that compares words that don’t sound alike:

SELECT 
    SOUNDEX('Ponzi'),
    SOUNDEX('Bracket'),
    SOUNDEX('Heavy')
FROM DUAL;

Result:

   SOUNDEX('PONZI')    SOUNDEX('BRACKET')    SOUNDEX('HEAVY') 
___________________ _____________________ ___________________ 
P520                B623                  H100

Exact Matches

Here’s an example of pairs of words that have a matching Soundex string, even though they’re different words, with different meanings:

SELECT 
    SOUNDEX('Dam') AS Dam, 
    SOUNDEX('Damn') AS Damn, 
    SOUNDEX('Too') AS Too, 
    SOUNDEX('Two') AS Two
FROM DUAL;

Result:

    DAM    DAMN     TOO     TWO 
_______ _______ _______ _______ 
D500    D500    T000    T000

Database Example

Here’s an example of getting the Soundex string from a database query:

SELECT 
    country_name, 
    SOUNDEX(country_name)
FROM countries
FETCH FIRST 10 ROWS ONLY;

Result:

   COUNTRY_NAME    SOUNDEX(COUNTRY_NAME) 
_______________ ________________________ 
Argentina       A625                     
Australia       A236                     
Belgium         B425                     
Brazil          B624                     
Canada          C530                     
Switzerland     S326                     
China           C500                     
Germany         G655                     
Denmark         D562                     
Egypt           E213

We can also use SOUNDEX() in a WHERE clause to return only those rows that sound like a given word:

SELECT
    employee_id,
    first_name,
    last_name
FROM employees
WHERE SOUNDEX(first_name) = SOUNDEX('Stephen');

Result:

   EMPLOYEE_ID    FIRST_NAME    LAST_NAME 
______________ _____________ ____________ 
           100 Steven        King         
           128 Steven        Markle       
           138 Stephen       Stiles

Empty String

Here’s what happens when an empty string is passed for each given argument:

SET NULL 'null';
SELECT SOUNDEX('')
FROM DUAL;

Result:

null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Null Argument

Passing null returns null:

SELECT SOUNDEX(null)
FROM DUAL;

Result:

null

Missing Argument

Calling SOUNDEX() without passing any arguments results in an error:

SELECT SOUNDEX()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT SOUNDEX()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

And passing too many arguments also results in an error:

SELECT SOUNDEX('Gosh', 'Dang')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT SOUNDEX('Gosh', 'Dang')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: