ALTER function get_semblance_By_2words
(
@word1 varchar(50),
@word2 varchar(50)
)
returns nvarchar(4000)
as
begin
declare @re int
declare @temp varchar(50)
declare @maxLenth int
declare @i int,@l int
declare @tb1 table(child varchar(50))
declare @tb2 table(child varchar(50))
set @i=1
set @l=2
set @maxLenth=len(@word1)
if len(@word1)<len(@word2)
begin
set @maxLenth=len(@word2)
end
-- set @word1=replace(@word1,' ','')
--set @word2=replace(@word2,' ','')
while @l<=len(@word1)
begin
while @i<len(@word1)
begin
set @temp=SUBSTRING(@word1,@i,@l)
if not exists(select child from @tb1 where child=@temp)
begin
insert @tb1 (child) values( @temp )
end
set @i=@i+1
end
set @i=1
set @l=@l+1
end
set @i=1
set @l=2
while @l<=len(@word2)
begin
while @i<len(@word2)
begin
set @temp=SUBSTRING(@word2,@i,@l)
if not exists(select child from @tb2 where child=@temp)
begin
insert @tb2 (child) values( @temp )
end
set @i=@i+1
end
set @i=1
set @l=@l+1
end
select @re=isnull(max( len(a.child)*100/ @maxLenth ) ,0) from @tb1 a, @tb2 b where a.child=b.child
return @re
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE function get_semblance_By_2words
(
@word1 varchar(50),
@word2 varchar(50)
)
returns nvarchar(4000)
as
begin
declare @re int
declare @maxLenth int
declare @i int,@l int
declare @tb1 table(child varchar(50))
declare @tb2 table(child varchar(50))
set @i=1
set @l=2
set @maxLenth=len(@word1)
if len(@word1)<len(@word2)
begin
set @maxLenth=len(@word2)
end
while @l<=len(@word1)
begin
while @i<len(@word1)-1
begin
insert @tb1 (child) values( SUBSTRING(@word1,@i,@l) )
set @i=@i+1
end
set @i=1
set @l=@l+1
end
set @i=1
set @l=2
while @l<=len(@word2)
begin
while @i<len(@word2)-1
begin
insert @tb2 (child) values( SUBSTRING(@word2,@i,@l) )
set @i=@i+1
end
set @i=1
set @l=@l+1
end
select @re=isnull(max( len(a.child)*100/ @maxLenth ) ,0) from @tb1 a, @tb2 b where a.child=b.child
return @re
end
不深究细节,提供一个最简单的比较函数:
-------------------------------------------------------------------------------------------------------------------------------
create function F_StrCompare(@str1 varchar(8000),@str2 varchar(8000))
returns int
as
begin
declare @cnt int,@num int
select @cnt=0,@num=(case when len(@str1)>len(@str2) then len(@str1) else len(@str2) end)
while @num>0
begin
if substring(@str1,@num,1)=substring(@str2,@num,1)
set @cnt=@cnt+1
set @num=@num-1
end
return @cnt
end
go
declare @t table(id int,[desc] varchar(1000))
insert into @t select 1,'我的家在这里?'
insert into @t select 1,'我的家在浙江?'
select top 1 * from @t order by dbo.F_StrCompare('我的家在哪里?',[desc]) desc
/*
id desc
----------- -----------------------
1 我的家在这里?
*/
如表格A中字段a1有一条字符串记录Rec1内容为 “我的家在哪里?”;然后要从B表中找出一条字符串记录Rec2内容与Rec1最相似的,如:“我的家在浙江?”
select A.a1, max(DIFFERENCE(A.a1,B.相应字段))
from A cross join B
group by A.a1