一个sql 判断值的问题
os:win7
db:sql2008r2
declare @fl varchar(1000)
select @fl='张三,李四,王五,赵倩,孙俪,李小璐,王小龙'
select @fl
希望能判断出 '李四,王小龙' 是否包含 @fl字段中
例如'李四,王小龙' 为包含
'李四,赵晓光' 为不包含
[解决办法]
SQL Server CHARINDEX和PATINDEX都可以
[解决办法]
-- 创建表值函数
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnc_SplitList] (@ListParam nvarchar(max), @Delim char(1))
RETURNS @Values TABLE (Item nvarchar(100))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(100)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@ListParam)
IF @chrind > 0
SELECT @Piece = LEFT(@ListParam ,@chrind - 1)
ELSE
SELECT @Piece = @ListParam
INSERT @Values(Item) VALUES(@Piece)
SELECT @ListParam = RIGHT(@ListParam ,LEN(@ListParam ) - @chrind)
IF LEN(@ListParam ) = 0 BREAK
END
RETURN
END
-- 调用函数
DECLARE @fl varchar(1000), @test varchar(500), @check varchar(1)
SET @check = 'Y'
SET @fl='张三,李四,王五,赵倩,孙俪,李小璐,王小龙'
SET @test = '李四,赵晓光'
IF EXISTS (SELECT 1 FROM (SELECT Item FROM dbo.fnc_SplitList(@test,',')) A WHERE A.Item NOT IN (SELECT Item FROM dbo.fnc_SplitList(@fl,',')) )
SET @check = 'N'
PRINT @check
