In MariaDB, you can use SOUNDS LIKE
in a query to match words that sound alike.
Syntax
The syntax goes like this:
expr1 SOUNDS LIKE expr2
It’s the same as doing this: SOUNDEX(expr1) = SOUNDEX(expr2)
.
Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English. 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.
Example
Here’s a basic example:
SELECT 'Two' SOUNDS LIKE 'Too';
Result:
+-------------------------+ | 'Two' SOUNDS LIKE 'Too' | +-------------------------+ | 1 | +-------------------------+
In this case, the two words sound the same.
We can use the SOUNDEX()
function to return the Soundex string of each word:
SELECT
SOUNDEX('Two'),
SOUNDEX('Too');
Result:
+----------------+----------------+ | SOUNDEX('Two') | SOUNDEX('Too') | +----------------+----------------+ | T000 | T000 | +----------------+----------------+
The Soundex strings are identical. This is why SOUNDS LIKE
returned 1
.
No Match
Here’s an example of two words that don’t sound the same:
SELECT 'Cat' SOUNDS LIKE 'Dog';
Result:
+-------------------------+ | 'Cat' SOUNDS LIKE 'Dog' | +-------------------------+ | 0 | +-------------------------+
In this case we got 0
, because the words don’t sound the same. Presumably, their Soundex strings are different. Let’s find out:
SELECT
SOUNDEX('Cat'),
SOUNDEX('Dog');
Result:
+----------------+----------------+ | SOUNDEX('Cat') | SOUNDEX('Dog') | +----------------+----------------+ | C300 | D200 | +----------------+----------------+
Yep. Different Soundex strings.
Database Example
Here’s an example of using SOUNDS LIKE
in the WHERE
clause of a database query:
SELECT
PetName,
SOUNDEX(PetName),
SOUNDEX('Wagg')
FROM Pets
WHERE PetName SOUNDS LIKE 'Wagg';
Result:
+---------+------------------+-----------------+ | PetName | SOUNDEX(PetName) | SOUNDEX('Wagg') | +---------+------------------+-----------------+ | Wag | W200 | W200 | +---------+------------------+-----------------+
Here it is using the SOUNDEX()
function to do the same thing:
SELECT
PetName,
SOUNDEX(PetName),
SOUNDEX('Wagg')
FROM Pets
WHERE SOUNDEX(PetName) = SOUNDEX('Wagg');
Result:
+---------+------------------+-----------------+ | PetName | SOUNDEX(PetName) | SOUNDEX('Wagg') | +---------+------------------+-----------------+ | Wag | W200 | W200 | +---------+------------------+-----------------+