Separate substring by comma SQL Server

Question

I have string from which I have to extract substring and query on their values.

declare @str varchar(max)
@str='Hello,world,continent,nation,city'

select * from mytable
where col_word in(SELECT REPLACE(@str,',',''','''))

The sub query

SELECT REPLACE(@str,',',''',''')

results in

Hello,'world','continent','nation','city

I want the above result be enclosed by single quotes so that it can work for IN

But this returns only for first col_word value Hello which is first substring in @str.

What should I do ?

I want the above result be enclosed by single quotes so that it can work for IN — Mudassir Hasan, Nov 23 '12 at 12:37
There is a world of difference betweeen `IN ('a','b','c')` and `IN (@myString)`. No matter what `@mystring` looks like, it is still ***one*** string and *not* a ***list*** of items. Search SO and the internet for SplitString functions, and then try this... `IN (SELECT * FROM fn_split(@myString))`. *[There are many split string functions on the web, they take a string, and return a table where each row is one item from your list. Then, having multiple items, your IN condition will work.]* — MatBailie, Nov 23 '12 at 13:29

score 3 · Accepted Answer · edited Nov 23 '12 at 13:16

3

Try this:

You cannot make part of your query as string. We have to make the whole query as a string, then execute it with EXEC() command.. or sp_executesql stored procedure. The latter is recommended.

declare @str varchar(max);
select @str='Hello,world,continent,nation,city';

SELECT @str=''''+REPLACE(@str,',',''',''')+''''
exec('select * from mytable where col_word in('+@str +')')

edited Nov 23 '12 at 13:16

Andriy M

76,112
17
94
154

answered Nov 23 '12 at 12:38

Joe G Joseph

23,518
5
56
58

score 1 · Answer 2 · answered Nov 23 '12 at 12:45

Try this:

declare @str varchar(max)
declare @pattern varchar(max)
SET @str='Hello,world,continent,nation,city'
SELECT REPLACE(@str,',',''',''')
SET @pattern = REPLACE('Hello,world,continent,nation,city', ',', ''',''')
EXEC('select * from mytable where col_word in(''' + @pattern + ''')')

Separate substring by comma SQL Server

2 Answers2

Linked