Sql server名称模糊 [英] Sql server fuzziness in the names

问题描述

我有一个名为customer的表，看起来像这样

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
);

现在我的要求就像我需要根据customername列搜索客户的数据，我使用的是我从前端调用的存储过程。前端是在asp.net

过程看起来像

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

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

在我的前端，我有一个文本框。当我在文本框中输入名称并在按钮上单击时，我会调用getdetails存储过程。



假设客户名称类似于pravin并且在文本框中而不是pravin我进入praveen，我仍然必须能够访问pravin的所有细节。从逻辑上讲，它可以使用模糊逻辑完成，您可以使用输入的名称映射名称的可能性。如果模糊度> 0.5，则显示名称。我想以编程方式实现它。我应该怎么做？

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?

推荐答案

一种已知的方法是基于 Levenshtein距离。请参阅：

http://en.wikipedia.org/wiki/Levenstein_distance [< a href =http://en.wikipedia.org/wiki/Levenstein_distancetarget =_ blanktitle =New Window> ^ ]，

http://en.wikipedia.org/wiki/Fuzzy_string_searching [ ^ ]。



我不知道任何与MS SQL有关的东西，但是你可以做你的搜索。 DB2中有模糊搜索功能，PostgreSQL，MySQL等有Levenshtein距离实现。

-SA

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

我改变了表并将其重写为

I altered the table and rewrote it as

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

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

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;

这给了我一个更好的co相关输出

This gives me a better co related output

@sergey：我实际上尝试了levenstein的东西距离。代码看起来像这样

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

/****** 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

这是你之前提到过的levenstein距离实现，我相信这是比soundex财产更好。但我还是觉得逻辑上有一个小错误。你能帮帮我吗



问候

Rakshith

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名称模糊的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！