select ID,name,SUBSTRING(key,1,charindex(key,',',1)) key from tb
if OBJECT_ID('test') is not null drop table test go create table test ( id int, name varchar(10), [key] varchar(20) ) go insert test select 1,'lisa','li,is,sa' union all select 2,'sophia','ab,cd,ef' union all select 3,'lori','12,34,23' goselect id, a.name, SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key] from test a,master..spt_values where number >=1 and number<len([key]) and type='p' and substring(','+[key],number,1)=',' /* id name key ----------------------------- 1 lisa li 1 lisa is 1 lisa sa 2 sophia ab 2 sophia cd 2 sophia ef 3 lori 12 3 lori 34 3 lori 23 */ --很少见到有人写这样的方法 试了一下 可以实现
select ID,name,SUBSTRING(key,1,charindex(',',key)-1) key from tb
全文搜索 select * from Tb where Contains(name, REPLACE(key, ',', ' Or '))
参考这个
from tb
drop table test
go
create table test
(
id int,
name varchar(10),
[key] varchar(20)
)
go
insert test
select 1,'lisa','li,is,sa' union all
select 2,'sophia','ab,cd,ef' union all
select 3,'lori','12,34,23'
goselect
id,
a.name,
SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key]
from
test a,master..spt_values
where
number >=1 and number<len([key])
and type='p'
and substring(','+[key],number,1)=','
/*
id name key
-----------------------------
1 lisa li
1 lisa is
1 lisa sa
2 sophia ab
2 sophia cd
2 sophia ef
3 lori 12
3 lori 34
3 lori 23
*/
--很少见到有人写这样的方法 试了一下 可以实现
from tb
select * from Tb where Contains(name, REPLACE(key, ',', ' Or '))