Tuesday 30 July 2019

MySQL SOUNDEX() Examples

One of the many MySQL string functions is the SOUNDEX() function. This function returns a Soundex string from a given string. If two words sound the same, they should have the same Soundex string. If two words sound similar, but not exactly the same, their Soundex string might look similar but not exactly the same.
This article contains a bunch of Soundex examples to demonstrate how the SOUNDEX()function works in MySQL.

Syntax

First, let’s look at the syntax:
SOUNDEX(str)
Where str is the string to which you require the Soundex string.

Example

Here’s an example of retrieving the Soundex string from a string:
SELECT SOUNDEX('Sure');
Result:
+-----------------+
| SOUNDEX('Sure') |
+-----------------+
| S600            |
+-----------------+
So in this case, the word Sure has a Soundex string of S600.

Example – Exact Match

Here’s an example of where two words sound the same (or very similar), and therefore, they share the same Soundex string:
SELECT 
    SOUNDEX('Sure') AS Sure, 
    SOUNDEX('Shore') AS Shore;
Result:
+------+-------+
| Sure | Shore |
+------+-------+
| S600 | S600  |
+------+-------+
Here are some more exact match examples:
SELECT 
    SOUNDEX('Dam') AS Dam,
    SOUNDEX('Damn') AS Damn,
    SOUNDEX('Too') AS Too,
    SOUNDEX('Two') AS Two;
Result:
+------+------+------+------+
| Dam  | Damn | Too  | Two  |
+------+------+------+------+
| D500 | D500 | T000 | T000 |
+------+------+------+------+

Example – Non Match

Here’s an example of where two words don’t sound the same, and therefore, they have different Soundex strings:
SELECT 
    SOUNDEX('Water') AS Water, 
    SOUNDEX('Coffee') AS Coffee;
Result:
+-------+--------+
| Water | Coffee |
+-------+--------+
| W360  | C100   |
+-------+--------+
As you can see, the Soundex string is completely different for these two words.

Example – Different Spellings

Here’s an example of two words that have different spellings (depending on which country you’re from):
SELECT 
  SOUNDEX('Color') AS 'Color',
  SOUNDEX('Colour') AS 'Colour';
Result:
+-------+--------+
| Color | Colour |
+-------+--------+
| C460  | C460   |
+-------+--------+
So we can see that such words will share the same Soundex string (as long as they’re pronounced the same way).

Example – Same Sound, Different Soundex

There are cases where words sound the same, but they have different Soundex strings. The most common reason for this is that they start with a different letter, one of which is a silent letter. You might’ve noticed from the previous examples that the Soundex string starts with the first letter of the string.
Therefore, if you have two words that are pronounced exactly the same, but they start with a different letter, they’ll have a different Soundex string.
Here are some examples:
SELECT
    SOUNDEX('Hole') AS 'Hole',
    SOUNDEX('Whole') AS 'Whole',
    SOUNDEX('Our') AS Our,
    SOUNDEX('Hour') AS Hour;
Result:
+------+-------+------+------+
| Hole | Whole | Our  | Hour |
+------+-------+------+------+
| H400 | W400  | O600 | H600 |
+------+-------+------+------+
The pairs in this example have different Soundex strings solely because their first letter is different.

Example – Soundex in a Database Query

Here’s an example of using SOUNDEX() in a database query. In this case, we’re looking for any records that sound like “Ay See Dee Ci”:
SELECT ArtistName 
FROM Artists
WHERE SOUNDEX(ArtistName) = SOUNDEX('Ay See Dee Ci');
Result:
+------------+
| ArtistName |
+------------+
| AC/DC      |
+------------+
So AC/DC apparently has the same Soundex code as Ay See Dee Ci (at least when using MySQL) Just to be sure, here’s the Soundex codes for both of those strings:
SELECT 
  SOUNDEX('AC/DC') AS 'AC/DC',
  SOUNDEX('Ay See Dee Ci') AS 'Ay See Dee Ci';
Result:
+-------+---------------+
| AC/DC | Ay See Dee Ci |
+-------+---------------+
| A232  | A232          |
+-------+---------------+

An Alternative: SOUNDS LIKE

An alternative query could have been constructed using SOUNDS LIKE instead of the SOUNDEX() function. Like this:
SELECT ArtistName FROM Artists
WHERE ArtistName SOUNDS LIKE 'Ay See Dee Ci';
Result:
+------------+
| ArtistName |
+------------+
| AC/DC      |
+------------+

0 comments:

Post a Comment