What is Soundex() function?

Soundex():

In real time we frequently use search criteria to search for Employee names from Employee table. Below are the few examples.

——————————————————————
SELECT * FROM [HumanResources].[Employee]
WHERE [JobTitle] = ‘Chief Executive Officer’
——————————————————————
SELECT * FROM [HumanResources].[Employee]
WHERE [JobTitle] LIKE ‘%Design%’
——————————————————————

But very rarely we might get requirements to fetch the similar employee names. This means that we should fetch all the employee names whose pronunciation is similar. For example if we are searching for Chandu, we should get the records of Chandue and Candu also along with the records of Chandu. But this not possible with ‘=’ or ‘LIKE’. 

The only solution for this kind of scenarios is SOUNDEX() function.

Soundex() function generates and returns a 4 digit code for all the names whose pronunciation is similar. 

Ex:

—————————————————————
— Words with similar pronunciation
— generates the same code.
—————————————————————
SELECT SOUNDEX(‘Chandu’) AS Chandu
SELECT SOUNDEX(‘Chandue’) AS Chandue
SELECT SOUNDEX(‘Candu’) AS Candu
—————————————————————
— Words with different pronunciation
— generates the different code.
—————————————————————
SELECT SOUNDEX(‘Chandyl’) AS Chandyl
SELECT SOUNDEX(‘Chandar’) AS Chandar
SELECT SOUNDEX(‘Candus’) AS Candus
—————————————————————

Soundex Example

Happy Reading :)

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *