Split a string returned from a column into integers

Question

Let me explain... I have one table which has a column that contains a delimited list of numbers which need to be used to return data from another table. (Don't ask, I did not design this!). EG:

Set @DelimitedList = (Select stupid_column from Table_One)

This results in @DelimitedList containing '1,2,3'; I then want to use this in a 'Where' clause for a subsequent query... i.e

Select * from Table_Two where int_column in (@DelimitedList)

Sadly this does not work as SQL tries to convert @DelimitedList to an integer which fails. Is there some way I can 'cast' or 'convert' my string list into an integer array?

Many thanks in advance

possible duplicate of [Split string in SQL](http://stackoverflow.com/questions/2647/split-string-in-sql) — Ian Nelson, Jan 14 '14 at 10:31
Is this MS-SQL? Can you do it in the application layer with two statements? — Jodrell, Jan 14 '14 at 11:15

score 2 · Answer 1 · answered Jan 14 '14 at 10:33

2

Using dynamic SQL, which I do not necessarily recommend, you may be able to do as follows:

EXEC ('Select * from Table_Two where int_column in (' + @DelimitedList + ')')

You can find more info, here.

answered Jan 14 '14 at 10:33

Thejaka Maldeniya

1,076
1
10
20

Thanks Thejaka, this does work but I think I favour valex's version below. I did try EXEC but got it all wrong so your solution has cleared up how I could have done it and will be useful in the future. – Nibbo Jan 15 '14 at 07:25

score 0 · Accepted Answer · answered Jan 14 '14 at 11:08

This can be done following way: Replace

WHERE    int_column in (@DelimitedList)

with

WHERE    ','+@DelimitedList+',' LIKE '%,'+CAST(int_column AS Varchar(10))+',%'

Also it can depend on your RDBMS. For example in MySQL you can use FIND_IN_SET() function.

Split a string returned from a column into integers

2 Answers2