Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Split a string and loop through the results

Posted on 2014-01-08
9
Medium Priority
?
392 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 41

Accepted Solution

by:
Sharath earned 1050 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 46

Expert Comment

by:Kent Olsen
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 46

Expert Comment

by:Kent Olsen
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 41

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

661 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