Sql server fuzziness in the names

Question

5.00/5 (2 votes)

See more:

SQL-Server-2008

I have a table called customer which looks something like this

SQL

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

SQL

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?

Posted 9-Oct-13 19:15pm

Rakshith Kumar

Add a Solution

4 solutions

Solution 4

SQL

This Query Gives Wt U Expected ,

Declare @LastName varchar(100) = 'D,S';

Select * From TableName
Where PATINDEX('%,' + CAST(LastName AS VARCHAR(20))+',%', ',' + @LastName + ',') > 0

Posted 9-Oct-13 20:56pm

VeeraAnnapureddy

Updated 9-Oct-13 20:59pm

v2

Comments

Rakshith Kumar 15-Oct-13 0:40am

can u please explain me how this works?
Regards
Rakshith

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Accepted Answer · 2013-10-09T19:32:00

Solution 1

One known approach is based on Levenshtein distance. Please see:
http://en.wikipedia.org/wiki/Levenstein_distance[^],
http://en.wikipedia.org/wiki/Fuzzy_string_searching[^].

I don't know anything specifically related to MS SQL, but you can do your search. There is a fuzzy search feature in DB2, Levenshtein distance implementations for PostgreSQL, MySQL, etc.

—SA

Posted 9-Oct-13 19:32pm

Sergey Alexandrovich Kryukov

Comments

Rakshith Kumar 10-Oct-13 1:37am

Thanks sergey. It was helpful :)

Sergey Alexandrovich Kryukov 10-Oct-13 1:51am

My pleasure. Will you accept the answer formally (green "Accept" button)? It won't prevent others from adding their answers.
—SA

Rakshith Kumar · Accepted Answer · 2013-10-09T20:26:00

I altered the table and rewrote it as

SQL

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

for this table i created a on insert trigger
something like

SQL

create trigger setsound on dbo.customer
for 
insert 
as
declare @customerid int;
declare @customername varchar(50);
declare @sound varchar(50);
select @customerid=customerid from inserted;
select @customername=customername from inserted
set @sound=(select soundex(@customername) from customer where customerid=@customerid)
update customer
set sound=@sound where customerid=@customerid;

now what i do is i rewrite the procedure

SQL

create procedure fuzziness(@customername varchar(50))
as
declare @sound varchar(50);
set @sound=(select sound from customer where customername=@customername);
select * from customer where sound=@sound;

This gives me a better co related output

Rakshith Kumar · Accepted Answer · 2013-11-21T18:33:00

@sergey : I actually tried something from the levenstein distance . Code looks something like this

SQL

/****** Object:  StoredProcedure [dbo].[flogic]    Script Date: 11/22/2013 10:20:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[flogic](@word1 varchar(20))
as
declare @word2 varchar(20);
declare @distance int;
declare @c int;
declare @i int;
declare @k int;
declare @len int;
declare @fuzzy decimal;
set @c=(select COUNT(firstname) from employee)
set @i=1;
while(@i<=@c)
begin
set @word2=(select firstname from employee where employeeid=@i)
set @k=1;
if(LEN(@word1)>=LEN(@word2))
set @len=LEN(@word1)
else
set @len=LEN(@word2)
while(@k<=@len)
begin
if(SUBSTRING(@word1,@k,1)!=SUBSTRING(@word2,@k,1))
begin
set @distance=@distance+1;
set @k=@k+1;
end
else
set @k=@k+1;
set @fuzzy=(@distance/@len)
IF(@fuzzy<=0.2)
RETURN @word2;
end
SET @i=@i+1;
END

This follows levenstein distance implimentation that you have mentioned earlier and i believe that this is better than soundex property. But still i feel that there is a small error in the logic. Could you please help me out

Regards
Rakshith

Sql server fuzziness in the names

4 solutions

Solution 1

Solution 3

Solution 5

Solution 4

Add your solution here

Preview 0