sqlServer中如何比较两个字符串相似！？

declare @string VARCHAR(20)

SET @string='ACC,IT'



declare @t table(col varchar(20))

insert @t

    select substring(@string,number,charindex(',',@string+',',number)-number)

    from master.dbo.spt_values

    where type='p'

       and substring(','+@string,number,1)=','



select *

from [tbl_job_adjusted_analysis] as a

  where exists(select * from @t 

               where charindex(','+col+',',','+a.C_DIV_ID+',')>0)

declare @s1 varchar(30),@s2 varchar(30)

set @s1='book,net,job'

set @s2='desk,job,linux,good,good'



create table tbl1(id int,keywords nvarchar(1000))

insert tbl1 select 1,@s1

union all select 2,@s2





select top 1000 id=identity(int,1,1) into 序数表 from syscolumns a,syscolumns b

alter table 序数表 add constraint pk_id_序数表 primary key(id)

go



select keyword=cast(substring(a.keywords,b.id,charindex(',',a.keywords+',',b.id)-b.id) as char(10))

,[count]=count(distinct a.id)

from tbl1 a,序数表 b

where b.id<=len(a.keywords)

and substring(','+a.keywords,b.id,1)=','

group by substring(a.keywords,b.id,charindex(',',a.keywords+',',b.id)-b.id)





--只要count列中有大于2的值就可以判断相似



/*

keyword    count

---------- -----------

book       1

desk       1

good       1

job        2

linux      1

net        1



(6 行受影响)



*/



drop table 序数表,tbl1

create table #t1 (col varchar(100))

create table #t2 (col varchar(100))



declare @string1 varchar(100),@string2 varchar(100)

set @string1='book,net,job'

set @string2='desk,job,linux,good'



set @string1='select '''+replace(@string1,',',''' union all select ''')+''''

set @string2='select '''+replace(@string2,',',''' union all select ''')+''''



insert #t1

exec(@string1)



insert #t2

exec(@string2) 



select *

from #t1 as t1 join #t2 as t2

  on t1.col=t2.col



drop table #t1,#t2

difference函数

返回一个整数值，指示两个字符表达式的 SOUNDEX 值之间的差异。

返回的整数是 SOUNDEX 值中相同字符的个数。返回的值从 0 到 4 不等：0 表示几乎不同或完全不同，4 表示几乎相同或完全相同。

SELECT difference('lihan','liha')

----------- 

3



（所影响的行数为 1 行）

declare @string = 'book,net.job';



--将字符串分解成:

col

---

book

net

job



---------------------------



declare @t table(col varchar(20));

insert @t

    select substring(@string,number,charindex(',',@string+',',number)-number)

    from master.dbo.spt_values

    where type='p'

       and substring(','+@string,number,1)=',';



--与数据库数据比较



select *

from tb as a

  where exists(select * from @t 

               where charindex(','+col+',',','+a.string+',')>0);

IF( 

SELECT DIFFERENCE ( 'book,net,job' ,'desk,job,linux,good' ) )>0 

PRINT '相似' 

ELSE 

PRINT '不相似' 



相似 

SELECT DIFFERENCE ( 'book,net,job' ,'desk,job,linux,good' ) 

            

----------- 

2



（所影响的行数为 1 行）