MySQL SOUNDEX() function

SOUNDEX() function

MySQL SOUNDEX() function returns soundex string of a string.

Soundex is a phonetic algorithm for indexing names after English pronunciation of sound. You can use SUBSTRING() on the result to get a standard soundex string. All nonalphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.

Syntax:

SOUNDEX(str)

Argument:

The SOUNDEX() function has following limitations :

• This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results.
• This function is not guaranteed to provide consistent results with strings that use multi-byte character sets, including utf-8.

Syntax Diagram:

MySQL Version: 8.0

Example: MySQL SOUNDEX() function

The following statement returns the SONDEX string of the argument string w3resource. The return value is W6262.

Code:

SELECT SOUNDEX('w3resource'); 

Output:

mysql> SELECT SOUNDEX('w3resource');
+-----------------------+
| SOUNDEX('w3resource') |
+-----------------------+
| W6262                 | 
+-----------------------+
1 row in set (0.00 sec)

Using SOUNDEX() in a query

The following MySQL statement returns all countries whose names have the same soundex code as 'USA'. It will retrieve countries like 'USA', 'UK', etc.

Code:

SELECT country
FROM author
WHERE SOUNDEX(country) = SOUNDEX('USA');

Sample table: author

Output:

country|
-------+
UK     |
UK     |
USA    |
UK     |
USA    |
USA    |
UK     |
USA    |

Soundex comparison with multiple strings

The following MySQL statement retrieve countries names from the "author" table that sound similar to 'USA' or 'Canada'.

Code:

SELECT country
FROM author
WHERE SOUNDEX(country) = SOUNDEX('USA')
or 
SOUNDEX(country) = SOUNDEX('Canada');

Sample table: author

Output:

country|
-------+
UK     |
Canada |
UK     |
USA    |
UK     |
USA    |
USA    |
Canada |
UK     |
USA    |

Using SOUNDEX() with LIKE

The following MySQL statement find all countries whose names have the same soundex code as 'US' as their starting portion.

Code:

SELECT country
FROM author
WHERE SOUNDEX(country) like CONCAT(SOUNDEX('US'),'%')

Sample table: author

Output:

country|
-------+
UK     |
UK     |
USA    |
UK     |
USA    |
USA    |
UK     |
USA    |

All String Functions (Slides presentation)

Previous: RTRIM
Next: SOUNDS_LIKE