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?
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?
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;
ENDASKER
I will give those a try next week. thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sharath, how do I loop through the results of exec sp_SplitString @string?
Hi YZlat,
If you're comfortable with recursive SQL, you might read this article:
https://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
If you're comfortable with recursive SQL, you might read this article:
https://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
ASKER
I'd prefer a solution for SQL Server, as i am not at all familiar with DB2
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
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
Can you explain more about your requirement? The sp_SplitString splits the string and gives the result.
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