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

  
+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT001 | William Norton       | UK        | Cambridge      |
| AUT002 | William Maugham      | Canada    | Toronto        |
| AUT003 | William Anthony      | UK        | Leeds          |
| AUT004 | S.B.Swaminathan      | India     | Bangalore      |
| AUT005 | Thomas Morgan        | Germany   | Arnsberg       |
| AUT006 | Thomas Merton        | USA       | New York       |
| AUT007 | Piers Gibson         | UK        | London         |
| AUT008 | Nikolai Dewey        | USA       | Atlanta        |
| AUT009 | Marquis de Ellis     | Brazil    | Rio De Janerio |
...........
| AUT015 | Butler Andre         | USA       | Florida        |
+--------+----------------------+-----------+----------------+  

View the table

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

  
+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT001 | William Norton       | UK        | Cambridge      |
| AUT002 | William Maugham      | Canada    | Toronto        |
| AUT003 | William Anthony      | UK        | Leeds          |
| AUT004 | S.B.Swaminathan      | India     | Bangalore      |
| AUT005 | Thomas Morgan        | Germany   | Arnsberg       |
| AUT006 | Thomas Merton        | USA       | New York       |
| AUT007 | Piers Gibson         | UK        | London         |
| AUT008 | Nikolai Dewey        | USA       | Atlanta        |
| AUT009 | Marquis de Ellis     | Brazil    | Rio De Janerio |
...........
| AUT015 | Butler Andre         | USA       | Florida        |
+--------+----------------------+-----------+----------------+  

View the table

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

  
+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT001 | William Norton       | UK        | Cambridge      |
| AUT002 | William Maugham      | Canada    | Toronto        |
| AUT003 | William Anthony      | UK        | Leeds          |
| AUT004 | S.B.Swaminathan      | India     | Bangalore      |
| AUT005 | Thomas Morgan        | Germany   | Arnsberg       |
| AUT006 | Thomas Merton        | USA       | New York       |
| AUT007 | Piers Gibson         | UK        | London         |
| AUT008 | Nikolai Dewey        | USA       | Atlanta        |
| AUT009 | Marquis de Ellis     | Brazil    | Rio De Janerio |
...........
| AUT015 | Butler Andre         | USA       | Florida        |
+--------+----------------------+-----------+----------------+  

View the table

Output:

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

All String Functions (Slides presentation)

PREV : RTRIM
NEXT : SOUNDS_LIKE