How to Compare String with Same Pronunciation in SQL Server

Here, I am going to teach you the necessary steps for how to compare strings with the same pronunciation in the SQL Server.

Here, I also listed the necessary coding lines that will help you to solve the SQL server compare string problem faster.

Basic Description

Sometimes we need to compare strings based on its same pronunciation. for such things, the SQL server Provides SOUNDEX function.

SOUNDEX function returns four character codes to evaluate the similarity of two strings.

SOUNDEX converts a string to a character code based on how sting is spoken and sound.

Why Use Soundex()?

• Suppose your table has various misspelled entries of users like “Smith”, and “Smythe”.
• Now you want to retrieve all records with similar pronunciation.
• For that situation, you can use Soundex().

Basic Soundex() Coding Rule

Every Soundex code contains a letter and three numbers, Such as a K-300. The First letter is the letter of your surname.

The all other numbers are used for the remaining letters of the surname according to the Soundex guide shown below

Kadiya is coded K-300 (K, 3 for the D, remaining letters disregarded).

Disregard the letters A, E, I, O, U, H, W, and Y.

Syntax:

SOUNDEX ( character_expression )

Arguments:

It is take alphanumeric expression character data.it can be constant, variables, columns and string.

Return Type:

Varchar

Example:

SELECT SOUNDEX (‘Smith’), SOUNDEX (‘Smythe’);

SELECT SOUNDEX (‘Skratch’), SOUNDEX (‘Skretch’);

SELECT FirstName FROM Users WHERE SOUNDEX(FirstName) = SOUNDEX(‘Smith’)

If you use a simple query like this:

SELECT FirstName FROM Users WHERE FirstName = ‘Smith’

then it will return the first row.

If you write a query using SOUNDEX function like this:

SELECT FirstName FROM Users WHERE SOUNDEX(FirstName) = SOUNDEX(‘Smith’)

then it will return those strings that have the same pronunciation.