How to Compare String with Same Pronunciation in SQL Server
Updated: October 5, 2018 | Technology: Asp.Net, ASP.NET Core, Asp.Net MVC, CMS System
How to Compare String with Same Pronunciation in SQL Server?
Here, I am going to teach you the necessary steps for how to compare string with the same pronunciation in the SQL Server. Here, I also listed the necessary coding lines which will help you to solve the problem faster.
Basic Description:
Sometimes we need to compare strings based on its same pronunciation. for such things, SQL server Provide SOUNDEX function.
SOUNDEX function returns four character code to evaluate the similarity of two strings.
SOUNDEX convert string to four character code based on how sting is spoken and sound.
Why Use Soundex()?
- Suppose your table has various misspelled entries of users like “Smith”, “Smythe”.
- Now you want to retrieve all records with similar pronunciation.
- For that situation, you can use Soundex().
Looking for Affordable Developer Support?
Make an inquiry and get the estimation for your requirement. Our .NET experts are ready to discuss your needs and problems.
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).
Number | Represents the Letters |
1 | B, F, P, V |
2 | C, G, J, K, Q, S, X, Z |
3 | D, T |
4 | L |
5 | M, N |
6 | R |
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 simple query like this:
SELECT FirstName FROM Users WHERE FirstName = ‘Smith’
then it will return the first row.
If you write query using SOUNDEX function like this:
SELECT FirstName FROM Users WHERE SOUNDEX(FirstName) = SOUNDEX(‘Smith’)
then it will return those strings which have the same pronunciation.