Solved

Split a string and loop through the results

Posted on 2014-01-08
9
377 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 45

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 41

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: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 45

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

738 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