Select record using comma separated string in Like operator in SQL Server

Last Reply 5 months ago By pandeyism

Posted 5 months ago


i have column in sql where i stored comma seperated values like


'cat, lion, donkey,'

in the sql query i want to search any one of the result show the data for e.g

select * from animal where type like '%'+ "cat, tiger," +'%" it should return above row and where ever it find cat or tiger in comma seperated column

but if search parameter are

select * from animal where type like '%'+ "zebra, dinosour," +'%"so in my example i do not have zebra or dinosour any where so it should not return any row

please advice how to acheive it

Posted 5 months ago Modified on 5 months ago

Hey nauna,

Please refer below sample query.


CREATE TABLE #animal (ID INT,type VARCHAR(max))
INSERT INTO #animal VALUES(1,'cat, lion, donkey,')
INSERT INTO #animal VALUES(2,'tiger, safari,')
SET @Key = 'cat,tiger'
SELECT @Key = 'LIKE ''%'+ REPLACE(@Key,',','%'' OR type LIKE ''%')+'%'''
EXEC('SELECT * FROM #animal WHERE type '+ @Key +'')
DROP TABLE #animal


ID type
1 cat, lion, donkey,
2 tiger, safari,