MySQL8 SOUNDEX() Functions – String Functions

by · Published · Updated

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.
Important :

While using this function, we should be aware of the 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 multibyte character sets, including utf-8.
Note :

This function implements the original Soundex algorithm, not the more popular enhanced version (also described by D. Knuth). The difference is that the original version discards vowels first and duplicates second, whereas the enhanced version discards duplicates first and vowels second.

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

SOUNDEX ( string );

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().

You may also like...