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

Last Reply 15 days ago By pandeyism

Posted 16 days ago

hello,

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 15 days ago Modified on 15 days ago

Hey nauna,

Please refer below sample query.

SQL

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

Output

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