CODEWITHSUNDEEP
sqlsql-server-2008split
Giri Prasad
Giri Prasad

Reputation: 1205

How to get the values of comma delimited string into a table?

I have a string @str =11,22 in a variable. how can I insert this into a table like,

 id      num
-------------
 1       11
 2       22

Upvotes: 0

Views: 46

Answers (1)

Christian Barron
Christian Barron

Reputation: 2755

Here's a simple way to get what you're looking for although it is dependent on the values being split by a comma and having no spaces (although you can use trim if you believe this could happen):

Declare @str varchar(10) = '11,22,33'
Declare @foobar table (id int identity (1,1), num int)

while (CHARINDEX(',', @str) > 0)
begin
insert into @foobar (num)
Select Left(@Str, CHARINDEX(',', @str) - 1)
Set @str = (select SUBSTRING(@str, CHARINDEX(',', @str) + 1, Len(@str)))
end
if Len(@Str) > 0
    insert into @foobar (num) 
    select @str

select * from @foobar

And here is the SQL Fiddle: http://www.sqlfiddle.com/#!6/0e240/2/0

Upvotes: 1

Related Questions