Solved

Split a string and loop through the results

Posted on 2014-01-08
9
365 Views
Last Modified: 2014-01-22
I need to create a procedure that acceps a varchar argument in form of a string, splits it on a comma and outputs the results in a loop.

For eaxmple the input argument is:  'Name1','Name2','Name3'

and output should be:

'Name1'
'Name2'
'Name3'

Each substring will be manipulated further inside the loop

Can anyone help?
0
Comment
Question by:YZlat
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 39765982
Hi YZlat,

The two most common ways to do this are to write a function that splits the strings into smaller strings, or a recursive query that splits the strings.  

The function is reusable but if this needs to be installed into a production environment, the DBA will probably need to create it for you.  If you've got a large amount of data to process, the recursive query could be slower.

My preference is usually the recursive query as it's portable.  It will run just fine with requiring the DBA to make database changes (create a function in the production environment).


Kent
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39765996
Try something like:
DECLARE @Input VARCHAR(500);
DECLARE @Item VARCHAR(20);
DECLARE @Delim CHAR(1);

SET @Input = 'Name1,Name2,Name3';
SET @Delim = ',';

SET @Input = @Input + ',';

WHILE CHARINDEX(@Delim, @Input, 0) <> 0
BEGIN
	SELECT @Item = SUBSTRING(@Input, 1, CHARINDEX(@Delim, @Input, 0)-1)
	SELECT @Input = SUBSTRING(@Input, CHARINDEX(@Delim, @Input, 0) + LEN(@Delim), LEN(@Input))
	
	PRINT @Item;
END

Open in new window

0
 
LVL 35

Author Comment

by:YZlat
ID: 39772426
I will give those a try next week. thank you
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Accepted Solution

by:
Sharath earned 350 total points
ID: 39774322
You can create an SP like this.
create proc sp_SplitString (@String varchar(500))
as
SELECT ltrim(SUBSTRING(string, n, CHARINDEX(',', string + ',',n) - n)) AS String
 FROM (select @string string) t1
CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
WHERE SUBSTRING(',' + string, n, 1) = ','
  AND n < LEN(string) + 1
go

Open in new window

And you can call the sproc like this.
declare @string varchar(100)
select @string = 'Name1,Name2,Name3'

exec sp_SplitString @string
/*
STRING
Name1
Name2
Name3
*/

Open in new window


http://sqlfiddle.com/#!3/7453e/1
0
 
LVL 35

Author Comment

by:YZlat
ID: 39798003
Sharath, how do I loop through the results of exec sp_SplitString @string?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39798027
Hi YZlat,

If you're comfortable with recursive SQL, you might read this article:

  http://www.experts-exchange.com/Database/DB2/A_3629-Recursive-SQL-in-DB2-Converting-Columns-to-Rows.html

It's written for DB2, but the changes to make it work for SQL Server are very small.  


Kent
0
 
LVL 35

Author Comment

by:YZlat
ID: 39798217
I'd prefer a solution for SQL Server, as i am not at all familiar with DB2
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39798357
The only two changes are that double-pipe (||) gets replaced by plus (+) as the two system use different concatenation operators, and the locate() function is replaced by charindex.

The advantage to this SQL is that it's portable and does not need a permissions to create a function or stored procedure.  The disadvantage is that recursive SQL can be intimidating.


Kent
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39798723
Can you explain more about your requirement? The sp_SplitString splits the string and gives the result.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question