Convert comma separated string values into table in SQL

In Sql server, we cannot manipulate with comma (,) separated string values in the ‘IN’ option of the WHERE clause.

In this article, Let us see how a solution can be provided for this requirement.

Example:

The below query results in error,

“Conversion failed when converting the varchar value ‘8, 9, 2, 12, 13, 58, 56, 57, 53, 54, 71, 69, 70, 269′ to data type int”

For that we need to convert the comma separated values into a table through the given query.

Declare @ids varchar(max)
select @ids='8, 9, 2, 12, 13, 58, 56, 57, 53, 54, 71, 69, 70, 269'
Select username from tbluser where uid in (@ids)

This query has to be modified by replacing all the comma (,) characters into “union all select “ and form an insert query to insert into a temp table and we have to get those values from the temp table.

I think this procedure will be very much helpful for sql developers,

--/************************************************
--Developer :Manivannan.S
--purpose:Query used to convert comma (,) separated
--            sting values into table
--***************************************************/
Declare @ids varchar(max)
--create a temp table with id column
create table #t (id int)
select @ids='8,9,2,12,13,58,56,57,53,54,71,69,70,269'
select @ids = 'insert #t select ' + replace(@ids, ',', ' union All select ')
print @ids
--Execute the Query
exec(@ids)
--select id from #t
--Use the temp table in inner of "in" keyword and get the result
Select username from tbluser where uid in (select id from #t)
--Finally drop the temp table
drop table #t