• # How to search comma separated values with comma separated?

Hi all,

How to search the comma separated values with comma separated values in SQL. For example:We have four columns in a table with four rows

in row1 we have 1,2,3
in row2 we have 2,3,4
in row3 5,4,3 and
in row4 we have 2,4,5

If we search 2, 3 then row1 and row2 should display.

Regards
Vivek Sharma
• Hi Vivek,

Try like this...

SELECT COLUMN1,COLUMN2,COLUMN3 FROM
(SELECT COLUMN1,COLUMN2,COLUMN3,(CONVERT(VARCHAR(10),COLUMN1)
+','+CONVERT(VARCHAR(10),COLUMN2)+','+CONVERT(VARCHAR(10),COLUMN3)) AS COLUMN4 FROM
(SELECT 1 AS COLUMN1,2 AS COLUMN2, 3 AS COLUMN3
UNION ALL
SELECT 2,3,4
UNION ALL
SELECT 5,4,3
UNION ALL
SELECT 2,4,5)A)B
WHERE COLUMN4 LIKE '%2,3%'

[OR]

SELECT * FROM
(SELECT '1,2,3' AS Column1
UNION ALL
SELECT '2,3,4'
UNION ALL
SELECT '5,4,3'
UNION ALL
SELECT '2,4,5')A
where Column1 like '%2,3%'

Regards,
Sri

• Thanks for your reply Sri, how to implement the same if we want to search 2, 4 w.r.t. above example?

Regards
Vivek Sharma

• Hi vivek,

I Update my previous post try that, if it is not working means please let me know and confirm me if you give 2,4 means you want to show row 2 and row 4?

Regards,
Sri

• Sri, I am not getting desire output, Yes 1,3 means we want to show row 1

• Hi Vivek,

Try this method...

SELECT COLUMN1,COLUMN2,COLUMN3 FROM
(SELECT COLUMN1,COLUMN2,COLUMN3,(CONVERT(VARCHAR(10),COLUMN1)
+','+CONVERT(VARCHAR(10),COLUMN2)+','+CONVERT(VARCHAR(10),COLUMN3)) AS COLUMN4 FROM
(SELECT 1 AS COLUMN1,2 AS COLUMN2, 3 AS COLUMN3
UNION ALL
SELECT 2,3,4
UNION ALL
SELECT 5,4,3
UNION ALL
SELECT 2,4,5)A)B
WHERE COLUMN4 LIKE replace('%2,4%',',','%')

[OR]

DECLARE @A VARCHAR(10)
SET @A='2,4' ----OR SET @A=replace('2,4',',','%')

SELECT COLUMN1,COLUMN2,COLUMN3 FROM
(SELECT COLUMN1,COLUMN2,COLUMN3,(CONVERT(VARCHAR(10),COLUMN1)
+','+CONVERT(VARCHAR(10),COLUMN2)+','+CONVERT(VARCHAR(10),COLUMN3)) AS COLUMN4 FROM
(SELECT 1 AS COLUMN1,2 AS COLUMN2, 3 AS COLUMN3
UNION ALL
SELECT 2,3,4
UNION ALL
SELECT 5,4,3
UNION ALL
SELECT 2,4,5)A)B
WHERE COLUMN4 LIKE replace('%'+@A+'%',',','%')

Regards,
Sri

• Hi Vivek...

Please try my above post definitely it will work...

Regards,
Sri

• Thanks for quick response, this is working little better, but the query fails in case the parameters changes like 12 , 13 , 14 etc. as you can understand in case like keyword.

Regards
Vivek Sharma

• Thank you vivek can you give some more example for that?

Regards,
Sri

• Sri, you can check the below example: I think it will not work with "like".

DECLARE @A VARCHAR(10)
SET @A='2,4' ----OR SET @A=replace('2,4',',','%')

SELECT COLUMN1,COLUMN2,COLUMN3 FROM
(SELECT COLUMN1,COLUMN2,COLUMN3,(CONVERT(VARCHAR(10),COLUMN1)
+','+CONVERT(VARCHAR(10),COLUMN2)+','+CONVERT(VARCHAR(10),COLUMN3)) AS COLUMN4 FROM
(SELECT 11 AS COLUMN1,12 AS COLUMN2, 13 AS COLUMN3
UNION ALL
SELECT 22,23,24
UNION ALL
SELECT 5,4,3
UNION ALL
SELECT 2,4,5)A)B
WHERE COLUMN4 LIKE replace('%'+@A+'%',',','%')

Regards
Vivek Sharma

• Hi vivek,

Try this proc...

Note : If you use below sp your input will check all the three columns....

for eg: if your input is '1,2'

it shows 1,2,3
3,1,2
1,3,2

ALTER PROC SAMPLESP(@A VARCHAR(100))
AS
BEGIN

DECLARE @B VARCHAR(100)
DECLARE @X VARCHAR(100),@Y VARCHAR(100),@Z VARCHAR(100),@LOOP INT

SET @A=@A+','

SET @LOOP=1
WHILE (CHARINDEX(',',@A,0)<>0)
BEGIN
SET @B=LEFT(@A,(CHARINDEX(',',@A,0)-1))
SET @A=SUBSTRING(@A,(CHARINDEX(',',@A,0)+1),LEN(@A)-LEN(@B))
IF @LOOP=1
SET @X=@B

ELSE IF @LOOP=2
SET @Y=@B

ELSE IF @LOOP=3
SET @Z=@B

SET @LOOP=@LOOP+1
END

--CHANGE YOUR TABLE AND COLUMN NAME HERE.........
--*****

SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLENAME

WHERE (COLUMN1=(CASE WHEN @X IS NULL THEN COLUMN1 ELSE @X END)
or COLUMN2=(CASE WHEN @X IS NULL THEN COLUMN2 ELSE @X END)
or COLUMN3=(CASE WHEN @X IS NULL THEN COLUMN3 ELSE @X END))

and (COLUMN1=(CASE WHEN @Y IS NULL THEN COLUMN1 ELSE @Y END)
or COLUMN2=(CASE WHEN @Y IS NULL THEN COLUMN2 ELSE @Y END)
or COLUMN3=(CASE WHEN @Y IS NULL THEN COLUMN3 ELSE @Y END))

and (COLUMN1=(CASE WHEN @Z IS NULL THEN COLUMN1 ELSE @Z END)
or COLUMN2=(CASE WHEN @Z IS NULL THEN COLUMN2 ELSE @Z END)
or COLUMN3=(CASE WHEN @Z IS NULL THEN COLUMN3 ELSE @Z END))

--*****
END

--EXEC SAMPLESP '1,2'

Regards,
Sri

• Hi Sri,

Our data is store in a single column there are no multiple columns. Like 1,2,3 are store in a single column. It can increase or decrease like the value can be like this:
Case 1: 1,2
Case 2: 1,2,3
Case 3: 2
Case 4: 1,2,3,5,4,6,7 etc.

Regards
Vivek Sharma

• Hi vivek,

Here i post one new procedure based on your requirement, there is no possible way to check middle value so
give multiple comma separated value like '1' to '1,2,3,4,5,6,7,8,9' and so on....
but give first value it is very importent ie. if your have row like this
1,2,3
2,3,4
1,2
2
1,2,3,4,5,6,7
11,12,13
11,13,14
21,23,24
21,25,26,27 then you must put first value ie. '1' or '2' or '11' or '21'
check like '1,2' or '2,4' or '1,2,3,4,7' or '11,12' or '11,13'
dont check with middle value like '26,27' or '13,14' etc

check it and let me know.......

Mark my post if helps....

CREATE PROC SAMPLESP(@A VARCHAR(100))
AS
BEGIN

----***********************************Change your table and column name here only********************
DECLARE @Column_Name VARCHAR(100)
DECLARE @Table_Name VARCHAR(100)

SET @Column_Name='Column1'
SET @Table_Name='TEMP'
----**********************************************************************************************

DECLARE @B VARCHAR(100)
DECLARE @X VARCHAR(MAX),@Y VARCHAR(MAX),@Z VARCHAR(100),@LOOP INT
CREATE TABLE #TEMP(Column1 VARCHAR(500))

IF(CHARINDEX(',',@A,0)=0)
BEGIN
SET @Y=(' OR '+@Column_Name+' = '+''''+CONVERT(VARCHAR(200),@A)+'''')
END
ELSE
SET @Y=''

SET @A= @A+','
SET @LOOP=1

WHILE (CHARINDEX(',',@A,0)<>0)
BEGIN
SET @B=LEFT(@A,(CHARINDEX(',',@A,0)-1))
SET @A=SUBSTRING(@A,(CHARINDEX(',',@A,0)+1),LEN(@A)-LEN(@B))
IF @LOOP=1
SET @B=@B+',%'
ELSE
SET @B='%,'+@B+'%'

insert into #temp select @B
SET @LOOP=@LOOP+1
END

SET @X=(SELECT 'Column1 LIKE '+''''+Column1+''''+' AND ' FROM #TEMP FOR XML PATH(''))

SET @X=(SELECT SUBSTRING(@X,1,LEN(@X)-4))

SET @X=('SELECT * FROM '+@Table_Name+' WHERE '+ @X + @Y )

EXEC (@X)

DROP TABLE #TEMP

END

/*

EXEC SAMPLESP '2,3,5'

*/

Regards
Sri