Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Split a string and loop through the results

Posted on 2014-01-08
9
Medium Priority
?
395 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

876 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