Looping Comma separated string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Looping Comma separated string

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-10-21 : 21:29:13

Hi,

I have a stored proc which has two input parameters.

@id bigint, @string nvarchar(max)

@string is comma delimited ex : "100,200,300,400,500".

I wanted to split this comma delimited string and insert into table "Sample" with same id.

sample input my proc :

1, "100,200,300,400,500"

desired output :

Table Name : sample:

1 100
1 200
1 300
1 400
1 500

i can split the data using function which returns as table.

Is there any way to avoid looping(while loop) of delimited data and insert into "sample" table

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-21 : 21:34:16

Use this splitter function (in Figure 21) http://www.sqlservercentral.com/articles/Tally+Table/72993/

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-22 : 06:01:17

Declare @ID1 bigint
set @ID1 = 1

DECLARE @str VARCHAR(2000) ='100,200,300,400,500'

DECLARE @strXML VARCHAR(8000) = '<table><row><col>' + REPLACE(REPLACE(@str,',','</col></row> <row><col>'),',','</col><col>') + '</col></row></table>'

DECLARE @XML XML = CAST(@strXML AS XML)

SELECT @ID1 AS ID,
line.col.value('col[1]', 'varchar(1000)') AS col1
FROM @XML.nodes('/table/row') AS line(col)

veeranjaneyulu

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-10-22 : 07:25:11

thank you james and veera.

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-22 : 07:41:57

Welcome

veeranjaneyulu

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-10-22 : 21:29:09

I am done with insert using the function DelimitedSplit8k made by jeff moden.


INSERT INTO sample
SELECT @id, Item FROM dbo.DelimitedSplit8K(@string, ',');

I am struck with how can i update the sample table with comma seperated values without using while loop. is there any way please suggest me

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-10-23 : 07:09:55

can anyone please suggest me

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 08:36:10

quote:
Originally posted by sqllover

I am done with insert using the function DelimitedSplit8k made by jeff moden.

INSERT INTO sample
SELECT @id, Item FROM dbo.DelimitedSplit8K(@string, ',');

I am struck with how can i update the sample table with comma seperated values without using while loop. is there any way please suggest me

I don't really understand what you are asking. The statement you already have is inserting into the sample table, isn't it? And, it is not using a loop.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-10-23 : 10:02:16

Hi james,

sorry for the confusion.i am done.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources