Thursday, March 7, 2013

How to split a comma separated string and loop it's values in SQL Server

Here is a simple T-SQL script for SQL Server that will split a comma separated string and loop on the values.
It's a simple way to create a array/list of things in SQL Server, and then do something on the values of the list.
These values can be anything: table names, stored procedures, query...

Application note:  the value list string must end with a comma ","
I've update the code to work even with a value list that doesn't have a trailing comma

DECLARE @valueList varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)

SET @valueList = 'aa,bb,cc,f,sduygfdctys,w,e,r,t,sd sdf sdf,yyy yyy yy,'

--the value list string must end with a comma ','
--so, if the last comma it's not there, the following IF will add a trailing comma to the value list
IF @valueList NOT LIKE '%,'
BEGIN
    set @valueList = @valueList + ','
END


set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
    set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
    set @value = SUBSTRING(@valueList, @pos, @len)
    --SELECT @pos, @len, @value /*this is here for debugging*/
        
    PRINT @value
    --Here is you value
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE

    set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END


The output of this script is:

    aa
    bb
    cc
    f
    sduygfdctys
    w
    e
    r
    t
    sd sdf sdf
    yyy yyy yy


SQL Code colored with this syntax highlighter