MySQL - SOUNDEX() Function

---

---

The MySQL SOUNDEX() function returns a phonetic representation of a given string.

It is used to compare and match the similarity of two strings based on their sounding or pronunciation rather than their actual spelling. The function assigns a code to each character of the string based on its pronunciation and then returns a four-character code that represents the overall pronunciation of the string.

We compare the soundings of two strings using the following: SOUNDEX(expr1) = SOUNDEX(expr2) that checks whether the SOUNDEX codes of two strings are equal. If the SOUNDEX codes match, it returns 1, otherwise 0.

Syntax

Following is the syntax of MySQL SOUNDEX() function −

SOUNDEX(str)

Parameters

This function takes a string value as a parameter.

Return Value

This function returns a four-character code representing the phonetic sound of the input string.

Example

In the following example, the SOUNDEX() function returns the phonetic representation of the string 'Hello how are you' as 'H460' −

SELECT SOUNDEX('Hello how are you');

Following is the output of the above code −

SOUNDEX('Hello how are you')
H460

Example

If you pass a numerical value as an argument, the function returns an empty string −

SELECT SOUNDEX(56789);

Output of the above code is as shown below −

SOUNDEX(56789)

Example

If you pass an empty string as an argument, the function returns an empty string −

SELECT SOUNDEX('');

The output obtained is as follows −

SOUNDEX('')

Example

When you pass a NULL value as an argument to this function, it returns NULL −

SELECT SOUNDEX(NULL);

The output obtained is as follows −

SOUNDEX(NULL)
0x

If you disable the --binary-as-hex value, the result of the above query will be as follows −

SELECT SOUNDEX(NULL);

The result produced is as follows −

SOUNDEX(NULL)
NULL

Example

Let us create a table named "SALES_TABLE" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE SALES_TABLE (
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   Price FLOAT,
   Location VARCHAR(255)
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO SALES_TABLE VALUES
('Key-Board', 'Raja', 900.56, 'Hyderabad'),
('Earphones', 'Roja', 2000.33, 'Vishakhapatnam'),
('Mouse', 'Puja', 1500.66, 'Vijayawada'),
('Mobile', 'Vanaja' , 9000.95, 'Chennai'),
('Headset', 'Jalaja' , 6000.99, 'Goa');

The SALES_TABLE obtained is as follows −

ProductName	CustomerName	Price	Location
Key-Board	Raja	900.56	Hyderabad
Earphones	Roja	2000.33	Vishakhapatnam
Mouse	Puja	1500.66	Vijayawada
Mobile	Vanaja	9000.95	Chennai
Headset	Jalaja	6000.99	Goa

Following query displays the soundex values of the column "CustomerName" of the SALES_TABLE −

SELECT ProductName, CustomerName, Price, Location, SOUNDEX(CustomerName) as RESULT
FROM SALES_TABLE;

After executing the above code, we get the following output −

ProductName	CustomerName	Price	Location	RESULT
Key-Board	Raja	900.56	Hyderabad	R200
Earphones	Roja	2000.33	Vishakhapatnam	R200
Mouse	Puja	1500.66	Vijayawada	P200
Mobile	Vanaja	9000.95	Chennai	V520
Headset	Jalaja	6000.99	Goa	J420

Example

Assume we have created another table named ANIMALS with 4 columns using the following query −

CREATE TABLE ANIMALS (
   id INT NOT NULL AUTO_INCREMENT,
   animal1 VARCHAR(50) NOT NULL,
   animal2 VARCHAR(50) NOT NULL,
   soundex_match INT NOT NULL,
   PRIMARY KEY (id)
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO ANIMALS (animal1, animal2, soundex_match) VALUES 
('cat', 'dog', SOUNDEX('cat') = SOUNDEX('dog')),
('elephant', 'eliphant', SOUNDEX('elephant') = SOUNDEX('eliphant')),
('bear', 'bare', SOUNDEX('bear') = SOUNDEX('bare')),
('snake', 'snail', SOUNDEX('snake') = SOUNDEX('snail'));

The "soundex_match" column is used to store the result of the SOUNDEX comparison between the two animal names. The result is 1 if the SOUNDEX codes match, else, 0 −

id	animal1	animal2	soundex_match
1	cat	dog	0
2	elephant	eliphant	1
3	bear	bare	1
4	snake	snail	0