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

Last Reply 13 days ago By pandeyism

Posted 14 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

You are viewing reply posted by: pandeyism 13 days ago.
Posted 13 days ago Modified on 13 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,