How the SOUNDS-LIKE() function works in Mariadb?

The SOUNDS LIKE operator in MariaDB is used to compare two strings based on their phonetic representations, similar to the SOUNDEX() function. It allows for the identification of strings that sound alike, which can be particularly useful in search queries where exact spelling may vary.

Syntax

The syntax for using the SOUNDS LIKE operator in MariaDB is as follows:

string1 SOUNDS LIKE string2

This operator takes two string arguments and returns a boolean value: 1 (true) if the strings sound similar, or 0 (false) otherwise.

Examples

Example 1: Basic Comparison

This example shows a basic comparison between two strings that sound similar.

SELECT 'Smith' SOUNDS LIKE 'Smythe';

The output for this statement is:

+------------------------------+
| 'Smith' SOUNDS LIKE 'Smythe' |
+------------------------------+
|                            1 |
+------------------------------+

This indicates that ‘Smith’ sounds like ‘Smythe’.

Example 2: Searching for Similar-Sounding Names

Here we use the SOUNDS LIKE operator to find names in a database that sound like a given name.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (id INT, name VARCHAR(100));
INSERT INTO employees VALUES (1, 'Smith'), (2, 'Smyth'), (3, 'Smythe'), (4, 'Smithson');

SELECT name FROM employees WHERE name SOUNDS LIKE 'Smith';

The output for this statement is:

+--------+
| name   |
+--------+
| Smith  |
| Smyth  |
| Smythe |
+--------+

The SOUNDS LIKE operator finds names that sound like ‘Smith’.

Example 3: Phonetic Search with Non-English Characters

This example demonstrates how the SOUNDS LIKE operator handles non-English characters.

SELECT 'José' SOUNDS LIKE 'Jose';

The output for this statement is:

+----------------------------+
| 'José' SOUNDS LIKE 'Jose'  |
+----------------------------+
|                          1 |
+----------------------------+

The SOUNDS LIKE operator considers ‘José’ and ‘Jose’ to sound similar, despite the accent.

Example 4: Joining Tables on Phonetic Matches

In this example, we join two tables based on phonetically similar city names.

DROP TABLE IF EXISTS cities;
CREATE TABLE cities (city_name VARCHAR(100));
INSERT INTO cities VALUES ('New York'), ('Nueva York');

DROP TABLE IF EXISTS visitors;
CREATE TABLE visitors (visitor_id INT, destination VARCHAR(100));
INSERT INTO visitors VALUES (1, 'New York'), (2, 'Nueva York');

SELECT v.visitor_id, c.city_name
FROM visitors v
JOIN cities c ON v.destination SOUNDS LIKE c.city_name;

The output for this statement is:

+------------+------------+
| visitor_id | city_name  |
+------------+------------+
|          1 | New York   |
|          2 | Nueva York |
+------------+------------+

Visitors are matched with cities based on phonetic similarity of their destinations.

Example 5: Limitations of Phonetic Comparison

This example highlights a limitation where different-sounding words may be considered similar.

SELECT 'Wright' SOUNDS LIKE 'Write';

The output for this statement is:

+------------------------------+
| 'Wright' SOUNDS LIKE 'Write' |
+------------------------------+
|                            0 |
+------------------------------+

Although ‘Wright’ and ‘Write’ have different pronunciations, they are considered phonetically similar by the SOUNDS LIKE operator.

Related Functions

Below are a few functions related to the MariaDB SOUNDS LIKE operator:

• MariaDB SOUNDEX() function is used to obtain a phonetic representation of a string.

Conclusion

The SOUNDS LIKE operator in MariaDB is a valuable tool for phonetic comparisons in search queries. It simplifies the process of finding similar-sounding strings without the need for exact spelling. However, users should be aware of its limitations and consider using additional conditions or functions for more precise results.