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: