这个是条件“CNN,谢瑞麟,合同”每个单词用逗号隔开
假如数据库里有三条数据
1.卡弗蒂道歉信仅101个单词 无CNN官方表态(图)
2.合同法实施草案引发热议 网友称部分规定
3.患心脏病被减刑 “珠宝大王”谢瑞麟判囚 怎么能都读出来 每个逗号隔开的一个都是关键词,就是一起读出相应的数据
假如数据库里有三条数据
1.卡弗蒂道歉信仅101个单词 无CNN官方表态(图)
2.合同法实施草案引发热议 网友称部分规定
3.患心脏病被减刑 “珠宝大王”谢瑞麟判囚 怎么能都读出来 每个逗号隔开的一个都是关键词,就是一起读出相应的数据
*
from
tb
where
charindex(','+'CNN,谢瑞麟,合同'+',',','+col+',')>0
SET @str = 'CNN,谢瑞麟,合同';SELECT
SUBSTRING(@str,number,CHARINDEX(',',@str + ',',number)-number) AS v
INTO #tmp
FROM master.dbo.spt_values
WHERE type = 'p' AND SUBSTRING(',' + @str,number,1)=',';SELECT * FROM tb AS A
WHERE EXISTS(SELECT * FROM #tmp WHERE CHARINDEX(v,A.col)>0);
select * from tb where charindex('CNN',字段)>0 union all
select * from tb where charindex('谢瑞麟',字段)>0 union all
select * from tb where charindex('合同',字段)>0
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
select 表.字段名
from 表 join (select col from dbo.f_splitSTR('CNN,谢瑞麟,合同',',')) k
on CHARINDEX(k.col,表.字段名)>0
SET @STR='CNN,谢瑞麟,合同'
SELECT * FROM TB WHERE
CHARINDEX(SUBSTRING(@STR,1,CHARINDEX(',',@STR)-1),COL)>0
OR
CHARINDEX(SUBSTRING(@STR,CHARINDEX(',',@STR)+1,3),COL)>0
OR
CHARINDEX(REVERSE(REVERSE(SUBSTRING(@STR,1,CHARINDEX(',',@STR)-1))),COL)>0取巧吧,先拆分就行了
DECLARE @T TABLE (NA VARCHAR(100))
INSERT INTO @T SELECT
'卡弗蒂道歉信仅101个单词 无CNN官方表态(图) ' UNION ALL SELECT
'合同法实施草案引发热议 网友称部分规定' UNION ALL SELECT
'患心脏病被减刑 “珠宝大王”谢瑞麟判囚 ' UNION ALL SELECT
'患心脏病被减刑 “珠宝大王”谢DD判囚 ' select * from @T where charindex('CNN',NA)>0 union all
select * from @T where charindex('谢瑞麟',NA)>0 union all
select * from @T where charindex('合同',NA)>0 NA
----------------------------------------------------------------------------------------------------
卡弗蒂道歉信仅101个单词 无CNN官方表态(图)
患心脏病被减刑 “珠宝大王”谢瑞麟判囚
合同法实施草案引发热议 网友称部分规定(3 行受影响)
WHERE EXISTS(SELECT * FROM (
SELECT SUBSTRING('CNN,谢瑞麟,合同',number,
CHARINDEX(',','CNN,谢瑞麟,合同' + ',',number)-number) AS v
FROM master.dbo.spt_values
WHERE type = 'p' AND SUBSTRING(',' + 'CNN,谢瑞麟,合同',number,1)=','
) AS T
WHERE CHARINDEX(v,A.col)>0);
set @sql='CNN,谢瑞麟,合同'
set @sql=N'select col=N'''+replace(@sql,',',''' union all select N''')+''''
INSERT TB exec (@sql)SELECT * FROM TB WHERE CHARINDEX(IDD,COL)>0