MySQL8 SOUNDEX() Functions – String Functions

SOUNDEX() Functions returns a Soundex string from a given input string.

Returns NULL if the argument is NULL.

1. If two words sound the same, they should have the same Soundex string. Example 1, 2.
2. If two words sound similar, but not exactly the same, their Soundex string might look similar but not exactly the same. Example 3.

Two strings that sound similar, the same should have identical Soundex strings. A standard Soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string.

You can use SUBSTRING() on the result to get a standard Soundex string.

• All nonalphabetic characters in a string are ignored.
• All international alphabetic characters outside the A-Z range are treated as vowels.

In this tutorial we will be covering some illustrations of similar sound words to make clear,  how the SOUNDEX() function operates in MySQL.

---

MySQL SOUNDEX() Functions: Syntax

---

MySQL SOUNDEX() Functions: Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|15%|55%” colalign=”left|left|left|left”]
Name, Required /Optional,Type, Description
String , Required, String , It represents valid string.
[/table]

---

MySQL SOUNDEX() Functions: Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Return, Description
Soundex string, of the input string.
NULL, if the argument is NULL.[/table]

---

MySQL SOUNDEX() Functions: Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 5.7
[/table]

---

SOUNDEX() Example 1 : Very easy example

We have to pass our string in the input parameter and we will get the SOUNDEX() value of the string. Below is the example to show it. It returns the same value if the input parameters sound are same.

mysql> SELECT SOUNDEX('mail'), SOUNDEX('male');
+-----------------+-----------------+
| SOUNDEX('mail') | SOUNDEX('male') |
+-----------------+-----------------+
| M400            | M400            |
+-----------------+-----------------+
1 row in set (0.10 sec)

---

SOUNDEX() Example 2 : Some more examples

Below are some more examples.

mysql> SELECT SOUNDEX('ball'), SOUNDEX('bawl');
+-----------------+-----------------+
| SOUNDEX('ball') | SOUNDEX('bawl') |
+-----------------+-----------------+
| B400            | B400            |
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT SOUNDEX('made'), SOUNDEX('maid');
+-----------------+-----------------+
| SOUNDEX('made') | SOUNDEX('maid') |
+-----------------+-----------------+
| M300            | M300            |
+-----------------+-----------------+
1 row in set (0.01 sec)

mysql> SELECT SOUNDEX('arc'), SOUNDEX('ark');
+----------------+----------------+
| SOUNDEX('arc') | SOUNDEX('ark') |
+----------------+----------------+
| A620           | A620           |
+----------------+----------------+
1 row in set (0.00 sec)

The above example returns the same value as input string sound is similar.

---

SOUNDEX() Example 3 : Same Sound, Different SOUNDEX value

There is some exception in the function also. The below illustration shows different Soundex values while the sound of both the input strings is the same. See below example :

mysql> SELECT SOUNDEX('ate'), SOUNDEX('eight');
+----------------+------------------+
| SOUNDEX('ate') | SOUNDEX('eight') |
+----------------+------------------+
| A300           | E230             |
+----------------+------------------+
1 row in set (0.00 sec)

---

SOUNDEX() Example 4 : NULL arguments

If the argument is NULL, it will return NULL. See below example :

mysql> SELECT SOUNDEX(NULL);
+---------------+
| SOUNDEX(NULL) |
+---------------+
| NULL          |
+---------------+
1 row in set (0.06 sec)

If the NULL is passed as a string in single quotes. Then, we will get its SOUNDEX() value.

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

---

See all MySQL String functions MySQL 8 String Functions.

---

Related articles : LOCATE(), HEX(), CONCAT(), CONCAT_WS() , LOWER(), LTRIM(), INSTR(), POSITION().

---

PHP Related articles : METAPHONE(), SOUNDEX(), PHP STRING FUNCTIONS(), SUBSTR_REPLACE().