MySQL SOUNDEX() with Examples

SOUNDEX() function in SQL language allows returning 4 characters according to the sound of a string. This output string is regularly used to evaluate the sound similarity between 2 strings. A common use is to use this SQL function to improve the relevance of an algorithm in case a user misspells a word.

Warning: by default, the system is based on English sounds. Therefore, there is no guarantee that it will work for other languages.
 

Syntax:

This function is used with the following syntax:

SELECT SOUNDEX('hello');

This SQL query returns the following results:

H400

 

 

Example :

Sound of “john” and “jone”

It is possible to evaluate the accuracy of these words in real life.

SELECT SOUNDEX('john'), SOUNDEX('jone');

Output:

+-----------------+-----------------+
| SOUNDEX('john') | SOUNDEX('jone') |
+-----------------+-----------------+
| J500            | J500            |
+-----------------+-----------------+

 

Search For a User Based On a Misspelled Name

In this tutorial, we will suppose an application using a database with a table of users.

+------------+-----------+--------+-------------------------------+
|     ID     |    Name   |  age   |             Address           |
+------------+-----------+--------+-------------------------------+
|     1      |   Alex    |   25   | 819 Saint Francis Way         |
|     2      |   Emily   |   15   | 171 Jarvisville Road Michigan |
|     3      |   John    |   35   | 188 Clay Street Indiana       |
|     4      |   Bob     |   40   | 285 Java Lane Missouri        |
+------------+-----------+--------+-------------------------------+

 

 
This application has its own search engine to look for users. SOUNDEX() function will get the list of users based on a name, as long as the name is pronounced the same way.

The query below is an example of a user searching for “john” with a wrong spelling, using the word “jone” instead.

SELECT id, name, age, address
FROM users
WHERE SOUNDEX(name) = SOUNDEX('jone')

Output:

+------------+-----------+--------+-------------------------------+
|     ID     |    Name   |  age   |             Address           |
+------------+-----------+--------+-------------------------------+
|     3      |   John    |   35   | 188 Clay Street Indiana       |
+------------+-----------+--------+-------------------------------+

This result shows that the function can help to find potentially relevant results.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More