Split a result by commas

  • in this table one column has entrys that look like : res1, res2, res3

    well i need to split these out to return each on it's own.

    [font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
    -Practical Magic[/font]

  • You can do this a few ways but it depends on the data that's being input.

    Do you know what kind of data is going to be input? Will it be from a predetermined list of values?

    Will they always follow the same format i.e. 4 characters then a comma Res1,Res2,Res3,Res4, ?

    Also what is the max amount of values that can be entered for that column?

    You also said they need to be output on their own. Does this mean you want them in a row of their own or in a column of their own (like a pivot table)?

  • Here is one way to do it:

    declare @Acc varchar(100),@AccString varchar(100)

    set @AccString = 'Res1,Res2,Res3,Res4' --or select @AccString = Column from YourTable

    --Create Temp Table

    IF Object_ID ('#Array') is null

    BEGIN

    Create Table #Array (Result varchar(8))

    END

    While len(@AccString) > 0

    BEGIN

    Set @Acc = left(@AccString,5) --Get first 5 chars

    set @AccString = replace(@AccString,@Acc,'') --Remove each result from string

    set @Acc = replace(@Acc,',','') --Remove comma from string

    --Insert Data

    Insert Into #Array

    select @Acc

    END

    select * from #Array

    Drop Table #Array

  • You can also check out the DelimitedSplit function in this thread as well.

  • the data will be diffrent, it's a list of menu choices. ie:

    links, admin, user, database, books

    and so on. no limit on how many. from what i understand one column is being used to store these choices, but they want to break them appart to display them as if they came out as diffrent records. I've already suggested just take what comes out and useing the frontend code to break it apart and they can do this, they just want to know if it's easier to do in sql.

    [font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
    -Practical Magic[/font]

  • How about posting the table DDL, sample data, and expected results. Please read the first article referenced in my signature block about asking for help. Follow the instruction in that article to get the best tested help quickly.

  • If the data is going to be different lengths then the code I provided would not work although it can be modified to do so.

    As far as the function Lynn posted I believe that won't work for SQL 2000 which this topic is under (SQL 7,2000).

    What version of SQL are you running?

  • AVB (8/4/2009)


    If the data is going to be different lengths then the code I provided would not work although it can be modified to do so.

    As far as the function Lynn posted I believe that won't work for SQL 2000 which this topic is under (SQL 7,2000).

    What version of SQL are you running?

    But the code in the following article will work... and it contains no explicit loops so it's also very fast.

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    There's a subsection near the end of the article that shows how to very quickly and easily split an entire table's worth of CSV's in a single, very simple SELECT.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    Good article. I'll have to try that next opportunity I get!

  • AVB (8/4/2009)


    Jeff,

    Good article. I'll have to try that next opportunity I get!

    Thanks for the feedback, AVB.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • purplebirky (8/3/2009)


    in this table one column has entrys that look like : res1, res2, res3

    well i need to split these out to return each on it's own.

    purplebirky,

    See the article I posted above.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply