假如有一个字符串String1="book,net,job"
数据库中存有字符串String2="desk,job,linux,good"
判断两个字符串相似的规则是:只要有一个单词相同就认为相似
String1和String2中都有"job",所以认为是相似的
用sql语句怎么来实现查询呢??
数据库中存有字符串String2="desk,job,linux,good"
判断两个字符串相似的规则是:只要有一个单词相同就认为相似
String1和String2中都有"job",所以认为是相似的
用sql语句怎么来实现查询呢??
-----------
2(所影响的行数为 1 行)
SELECT DIFFERENCE ( 'book,net,job' ,'desk,job,linux,good' ) )>0
PRINT '相似'
ELSE
PRINT '不相似'相似
SELECT DIFFERENCE ( 'book,net,job' ,'desk,job,linux,good' ) )>0
PRINT '相似'
ELSE
PRINT '不相似' 相似
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);
返回一个整数值,指示两个字符表达式的 SOUNDEX 值之间的差异。
返回的整数是 SOUNDEX 值中相同字符的个数。返回的值从 0 到 4 不等:0 表示几乎不同或完全不同,4 表示几乎相同或完全相同。
SELECT difference('lihan','liha')
-----------
3(所影响的行数为 1 行)
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.coldrop table #t1,#t2
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)
goselect 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
数据库中存有字符串String2="desk,job,linux,good"
先把String1拆分成数组,再用循环觉得楼主这个在程序更方便,用STRING2.INDEXOF(MY[I])>0就相似
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)
7楼的是可以,但是我还得加其他搜索条件