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: