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

Last Reply 2 months ago By pandeyism

Posted 2 months 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 2 months ago.
Posted 2 months ago Modified on 2 months 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,