查询的时候把";"" "同一替换成",":declare @keyword varchar(20)
set @keyword='音乐' --关键词select * from 新闻表
where charindex(','+@keyword+','
,
','+replace(replace(@keyword,' ',','),';',',')+','
)>0
set @keyword='音乐' --关键词select * from 新闻表
where charindex(','+@keyword+','
,
','+replace(replace(@keyword,' ',','),';',',')+','
)>0
insert test
select 1,'tvb,音乐;娱乐' union
select 2,'音乐;摇滚' union
select 3,'娱乐,绯闻' union
select 4,'欧美音乐 新闻 电影配乐' union
select 5,'下载 mp3;欧美' union
select 6,'电影,音乐'
goselect [id] from test where ','+classid+',' like '%[ ,;]音乐[ ,;]%'
/*
id
-----------
1
2
6(所影响的行数为 3 行)
*/
select [id] from test where ','+classid+',' like '%[ ,;]欧美音乐[ ,;]%'
/*
id
-----------
4(所影响的行数为 1 行)
*/
select [id] from test where ','+classid+',' like '%[ ,;]电影[ ,;]%'
/*
id
-----------
6(所影响的行数为 1 行)
*/
drop table test
go
set @keyword='音乐'
select * from test
where charindex(','+@keyword+','
,
','+replace(replace(replace(classid,' '','),';',','),',',',')+','
)>0