Pages

Friday, January 31, 2014

SQL Server: use a cursor to fetch rows, then split a comma separated string in a column, and loop all the values

Some time ago I wrote a post about "How to split a comma separated string and loop it's values in SQL Server".
Today an anonymous user asked how does this apply if you are reading comma separated string from a column in a DB. 

The simplest way would probably be to do these kind of thing in a proper programming language (C++, C#, VB, Java.... you name it)
But if you really need to do this in T-SQL you can simply use a SQL Server cursor to enumerate the rows of the table, and then split the string one-by-one.

So, suppose we have a column in a table that contain comma separated values, we ca use a stored procedure with the following code. (as of today this code is UNTESTED, just because I don't have a SQL Server instance installed on my home computer :-) )
DECLARE @valueList varchar(8000) 

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

DECLARE my_string_table_cursor CURSOR
    FOR SELECT my_column_string FROM my_table

OPEN my_string_table_cursor 


    FETCH NEXT FROM my_string_table_cursor
    INTO @valueList 

    WHILE @@FETCH_STATUS = 0
    BEGIN

        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 

        FETCH NEXT FROM my_string_table_cursor 
        INTO @valueList  

    END

CLOSE my_string_table_cursor
DEALLOCATE my_string_table_cursor
The table should contain a column with values like
    row 1:  aa,bb,cc,dwefwef,43truygtfuye,w,
    row 2:  trd , ygu , umhb , bhu,
    row 3:  a,b,c,d,e,f,g,h,i,
    row 4:  123,a,456,bbb,kkk,00000,
And the string in each row must end with a comma ","

SQL Code colored with this syntax highlighter

4 comments:

  1. It works. Many thanks.

    Annie

    ReplyDelete
  2. it works gr8, can you please tell me that how can I store these values in a temporary table or any other table because right now its printing the values in the Messages tab of the query window, and another thing that I noticed is when this query completes one string, it adds a blank line after that and then starts to generate the next string list... which part can I change to create a table(temporary) but without the blank lines

    ReplyDelete
  3. and I forgot to mention that the string that I am picking up from the database column has COMMA at the start and at the end of each record like this -----> ,10033836,9682244,9682245,9670415,9611823,10047554,8779396,9682325,9670448,9672430,8783473,9612160,9670452,8789112,9688440,

    ReplyDelete