Split a string and loop through the results

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?
LVL 35
YZlatAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
YZlatAuthor Commented:
I will give those a try next week. thank you
0
 
YZlatAuthor Commented:
Sharath, how do I loop through the results of exec sp_SplitString @string?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
YZlatAuthor Commented:
I'd prefer a solution for SQL Server, as i am not at all familiar with DB2
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
SharathData EngineerCommented:
Can you explain more about your requirement? The sp_SplitString splits the string and gives the result.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.