SQL - SOUNDEX() Function

---

---

The SQL SOUNDEX() function is used to retrieve the Soundex string for the string value.

It returns a Soundex value of a string. A Soundex string is a phonetic algorithm for indexing names after the English pronunciation of sound, a String of a String. Two Strings that sound almost the same should have identical Soundex strings. A standard Soundex string is four characters long.

The SOUNDEX() function accepts a string as a parameter. It returns a NULL value if we pass a NULL value as an argument to this function.

Note − All non-alphabetic characters in the given string are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.

Syntax

Following is the syntax of the SQL SOUNDEX() function −

SOUNDEX(exp)

Parameters

• exp − It is a expression from which the soundex string will be retrieved.

Return value

This function returns the Soundex string.

Example

In the following example,we are using the SOUNDEX() function to retrieve the Soundex String of the the String ‘Hello World’.

SELECT SOUNDEX('Hello World');

Output

On executing the above program, it will produce the following output −

+------------------------+
| SOUNDEX('Hello World') |
+------------------------+
| H4643                  |
+------------------------+

Example

Following is another example of the SOUNDEX() function, we are trying to retrieve the Soundex String of the the String ‘Tutorialspoint’.

SELECT SOUNDEX('TutorialsPoint');

Output

Following is the output of the above statement −

+---------------------------+
| SOUNDEX('TutorialsPoint') |
+---------------------------+
| T642153                   |
+---------------------------+

Example

If we pass a numeric value as an argument to this method, it returns an empty string or zero(0000) in the output.

In this program, we are passing a numeric value of 123212 to the SOUNDEX() function to retrieve the Soundex String for it.

SELECT SOUNDEX(123212);

Output

The above SQL statement produces the following output −

+-----------------+
| SOUNDEX(123212) |
+-----------------+
|     0000        |
+-----------------+

Example

If we pass an empty string(‘’) to the SOUNDEX() function, this function will return an empty string or zero(0000) in the output.

SELECT SOUNDEX('');

Output

After executing the above query, it produces the following output −

+-------------+
| SOUNDEX('') |
+-------------+
|             |
+-------------+

Example

In the following example,we are passing a NULL value as an argument to the SOUNDEX() function.

SELECT SOUNDEX(NULL);

Output

Following is the output of the above query −

+------------------------------+
| SOUNDEX(NULL)                |
+------------------------------+
| NULL                         |
+------------------------------+

Example

You can pass the table column as an argument to the SOUNDEX() function to retrieve the Soundex string. Assume we have created a table with the name Customers using the CREATE statement as follows −

CREATE TABLE CUSTOMERS(    
ID INT NOT NULL,    
NAME VARCHAR (20) NOT NULL,    
AGE INT NOT NULL,    
ADDRESS CHAR (25) ,    
SALARY DECIMAL (18, 2));

Now, let's insert four records in to the Customers table using the INSERT statement as shown below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

The following query displays the Soundex values of the column ADDRESS of the Customers table −

SELECT ID, NAME, SOUNDEX(ADDRESS) FROM CUSTOMERS;

Output

The above SQL query generate the following output −

+----+----------+------------------+
| ID | NAME     | SOUNDEX(ADDRESS) |
+----+----------+------------------+
|  1 | Ramesh   | A5313            |
|  2 | Khilan   | D400             |
|  3 | kaushik  | K300             |
|  4 | Chaitali | M100             |
+----+----------+------------------+