Pages

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 ","

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,'

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

29 comments:

  1. Very good job ! :)

    Saved my lot of time figuring out,
    which now I can save for the higher level of the project

    Thanks !
    Greets Jacob

    ReplyDelete
  2. tanks a lot
    you solved my problem
    good lock
    ^_^

    ReplyDelete
  3. What if I am reading a common separated string from a column in a table? How does the code apply? Thanks.

    ReplyDelete
    Replies
    1. Hi,
      I wrote a post with some sample code to show how to read the rows from the table using a cursor, and then split the values.
      Here is the post: http://techforpassion.blogspot.it/2014/01/sql-server-use-cursor-to-fetch-rows.html

      Delete
    2. Great job. Thanks a lot.

      Delete
  4. Thanks for posting this. Nice simple solution to my simple need.

    ReplyDelete
  5. Very Nice. thanks for posting this article.

    ReplyDelete
  6. Thank you so much
    This is really helpful and working perfectly

    ReplyDelete
  7. Thank you so much. you made my day!!

    ReplyDelete
  8. Gracie from 2015

    ReplyDelete
  9. Thanks Although it was not exactly what i was looking for but you gave me some idea where to start

    ReplyDelete
  10. just found this and used it inover 10 different parts of our database! thanks so much! great stuf and easy to understand!!

    ReplyDelete
  11. Just wanted to say thank you, simple elegant solution!

    ReplyDelete
  12. Thank you, this saved me a lot of time.

    ReplyDelete
  13. Thanks. That definitely save me some time!

    ReplyDelete
  14. Nice Article !
    This is my pleasure to read your article.
    Really this will help to people of Database Community.

    I have also prepared one article about, What should be our practice to store comma separated list in Database System.
    You can also visit my article, your comments and reviews are most welcome.
    http://www.dbrnd.com/2016/01/database-design-storing-a-comma-separated-list-in-a-database-is-a-bad-practice/

    ReplyDelete
  15. It did not work if there is no comma in the end of the string: SET @valueList = 'aa,bb,cc,f,sduygfdctys,w,e,r,t,sd sdf sdf,yyy yyy yy'

    ReplyDelete
    Replies
    1. Sure, I know, that's why I added the text "Application note: the value list string must end with a comma ','" just before the SQL :-)

      Delete
    2. I am looking for a solution for same strings but that not ends with the comma, do you have any suggestion ?

      Delete
    3. if @valueList not like '%,'
      begin
      set @valueList = @valueList + ','
      end

      Delete