CODEWITHSUNDEEP
sql-server
Jad Chahine
Jad Chahine

Reputation: 7149

SQL Server fuzziness in the names

I have a table called customer which looks something like this:

create table customer
(
    customerid int identity(1,1) primary key,
    customername varchar(50),
    caddress varchar(max),
    cemail varchar(30) unique
);

Now my requirement is something like I need to search the data of the customer based upon customername column for which I use a stored procedure which I call from the front end. Front end is in asp.net

Procedure looks something like this:

create procedure getdetails(@customername varchar(50))
as
   select * 
   from customer 
   where customername in (@customername);

In my front end I have a text box

When I enter the name in the textbox and on the button click I call the getdetails stored procedure.

Suppose the customername is something like pravin and in the textbox instead of pravin I enter praveen.

I must still be able to access all the details of pravin.

Logically it can be done using fuzzy logic where u can map the likeliness of the name with the entered name. If the fuzziness > 0.5 then display the names.

I want to implement it programatically. How should I do it?

Upvotes: 0

Views: 45

Answers (1)

philosp
philosp

Reputation: 36

What you are looking for is the function SOUNDEX(). The explanation on this page goes:

SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken.

So, you should get some results if you compare both values like this:

select * from customer where SOUNDEX(customername) = SOUNDEX(@customername);

Edit:

Also, if you want to go even "fuzzier", you could compare them with DIFFERENCE() function, which returns a value number. You could then decide on a threshold, like so:

select * from customer where DIFFERENCE(customername,@customername) < 5;

Upvotes: 1

Related Questions