22,300
社区成员




DECLARE @a VARCHAR(100)='1,2,4',
@b VARCHAR(100)='2,3,4,5,6',
@c VARCHAR(100)='1,7,8'
DECLARE @Flag BIT = 0,
@Pos INT,
@Var VARCHAR(5)
--判断 @a 和 @b
WHILE LEN(@a) > 0 AND @Flag = 0
BEGIN
SELECT @Pos= CHARINDEX(',',@a),
@Var = CASE WHEN @Pos > 0 THEN LEFT(@a,@Pos-1) ELSE @Var END,
@a = STUFF(@a,1,@Pos,'')
IF ','+ @b + ',' LIKE '%,' + @Var + ',%'
SET @Flag = 1
END
SELECT @a,@Flag
SELECT @Flag = 1
--判断 @c 和 @b
WHILE LEN(@c) > 0 AND @Flag = 1
BEGIN
SELECT @Pos= CHARINDEX(',',@c),
@Var = CASE WHEN @Pos > 0 THEN LEFT(@c,@Pos-1) ELSE @Var END,
@c = STUFF(@c,1,@Pos,'')
IF ','+ @b + ',' NOT LIKE '%,' + @Var + ',%'
SET @Flag = 0
END
SELECT @c,@Flag
DECLARE @a VARCHAR(200)='1,2,4'
DECLARE @b VARCHAR(200)='2,3,4,5,6'
DECLARE @c VARCHAR(200)='1,7,8'
DECLARE @sxml XML
SET @sxml='<root><node>'+REPLACE(@a,',','</node><node>')+'</node></root>'
--INSERT INTO @tmp([SPLIT])
SELECT b.value('.','varchar(max)') FROM @sxml.nodes('root/node') AS s(b)
WHERE CHARINDEX(b.value('.','varchar(max)'),@b)>1
IF @@ROWCOUNT>0
PRINT 'Found'
ELSE
PRINT 'Not Found'