SQL use in query for comma separated string

Question

I'm using a stored procedure and I pass it a string with comma like this:

Apple,Banana,Orange

I want to use this string inside an IN clause, but I don't want to use

SELECT value 
FROM STRING_SPLIT(@MyStr, ',')

How can I generate string that I can use like

SELECT * 
FROM MyTable 
WHERE Fruit IN (@MyStr)

https://stackoverflow.com/questions/35620646/comma-separated-string-using-cte — Lasse Edsvik, Apr 25 '19 at 18:28
@Faisal try splitting the string by delimiter first, then do a subquery search. Let me know if this doesn't work! — Cowthulhu, Apr 25 '19 at 18:31
SQL Server does not support macro substitution. In order to execute as you wish, you would need DYNAMIC SQL. — John Cappelletti, Apr 25 '19 at 18:35

Gordon Linoff · Accepted Answer · 2019-04-25T19:15:43.437

7

Just use a subquery:

select *
from mytable
where fruit in (select value from STRING_SPLIT(@MyStr, ','));

In older versions of SQL Server, you can use like:

select *
from mytable
where ',' + @MyStr + ',' like '%,' + fruit + ',%';

edited Apr 25 '19 at 19:15

answered Apr 25 '19 at 18:27

Gordon Linoff

its mentioned that i don't want to use string_split – Faisal Apr 25 '19 at 18:31
@Faisal - This answer works given the code in the question so why do you want to make it more difficult and omit a technically sound approach to solving your problem? – Igor Apr 25 '19 at 18:34
Because STRING_SPLIT is only available as of SQL 2016. Likely they are running an older server version. Dynamic SQL would be the way to go as an alternative. – Robert Sievers Apr 25 '19 at 18:45
1

He gave you a version for older sql that doesn't use string_split. – Susan Apr 25 '19 at 19:53
i'll try the older version – Faisal Apr 26 '19 at 06:40
yes worked perfect – Faisal Apr 29 '19 at 06:34

1 Answers1