Compare Comma Separated Value with Comma Separated Column in SQL SERVER

SET nocount ON

-- Declare a table and inserting some data with comma separated.

DECLARE @MyTable TABLE (Value VARCHAR(10))

INSERT INTO @MyTable VALUES('1,2,3')

INSERT INTO @MyTable VALUES('2,3,5')

INSERT INTO @MyTable VALUES('6,8,9')

-- Value to compared with comma separated column.

DECLARE @words VARCHAR(max) = '2,3,45,96'

DECLARE @split TABLE (word VARCHAR(64))

DECLARE @word VARCHAR(64),@start INT, @end INT, @stop INT

-- string split in 8 lines

SELECT @words += ',',@start = 1,@stop = Len(@words) + 1

WHILE @start < @stop

BEGIN

SELECT @end = Charindex(',', @words, @start),

@word = Rtrim(Ltrim(Substring(@words, @start, @end - @start))),

@start = @end + 1

INSERT @split VALUES (@word)

END

-- This will split the comma separated value like this:

-- Select the Records that matches the given crieteia.

SELECT *

FROM @split

SELECT *

FROM @MyTable a

WHERE EXISTS (SELECT *

FROM @split w

WHERE Charindex(',' + w.word + ',', ',' + a.Value + ',') > 0)

Output: