Post reply

SQL to split a non delimited string into delimited string

  • February 6, 2011 at 8:51 am

    #233964

    I have a text string, say '12345', and I need to comma delimit each character in the string, i.e. '1,2,3,4,5'

    I can do this easily enough with a loop, but I was wondering if there's an easier / more efficient way?

    Thanks

  • February 7, 2011 at 3:06 am

    #1283005

    you can do it through. stuff if it is a column value. e.g.

    STUFF((

    SELECT ',' + CONVERT(NVARCHAR(1000),pu.UserId)

    FROM dbo.WI_ProjectUsers pu

    WHERE pu.ProjectId = CTE.ProjectId

    ORDER BY pu.UserId

    FOR XML PATH(''),

    TYPE).value('.','varchar(max)'),1,1,''),

    follow the link to know more

    http://www.sqlservercentral.com/articles/comma+separated+list/71700/

  • February 7, 2011 at 3:53 am

    #1283019

    How about this:

    declare @string VARCHAR(100)

    SET @string = '12345'

    ;WITH

    TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

    THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),

    TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM THOUSANDS)

    select STUFF(

    (SELECT ','+SUBSTRING(@string,N,1)

    FROM TALLY T_Outer

    WHERE N <= LEN(@string)

    FOR XML PATH('') ),1 ,1 ,'') Concat_Values

  • February 7, 2011 at 4:41 am

    #1283028

    That's pretty slick! Thanks guys.

  • February 7, 2011 at 4:50 am

    #1283033

    You're welcome,Mr.Wolf 😉

  • February 7, 2011 at 6:32 am

    #1283064

    Wolfmeister (2/7/2011)

    That's pretty slick! Thanks guys.

    Just making sure... do you understand how ColdCoffee's method works?

    --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)

  • February 7, 2011 at 6:33 am

    #1283069

    ColdCoffee (2/7/2011)

    You're welcome,Mr.Wolf 😉

    Nicely done, CC.

    --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)

  • February 7, 2011 at 7:47 am

    #1283123

    If my understanding is correct then this method uses a tally table in conjunction with the SUBSTRING function to get the starting positions of each character in @string, and prefix each character with a comma. FOR XML PATH then concatenates the values into a single string, and finally the STUFF function then replaces the first comma with ''.

  • February 7, 2011 at 7:54 am

    #1283129

    Jeff Moden (2/7/2011)

    ColdCoffee (2/7/2011)

    You're welcome,Mr.Wolf 😉

    Nicely done, CC.

    Wow, accolades from the highest quarters 🙂 Thanks , Jeff. 🙂

  • February 7, 2011 at 7:55 am

    #1283130

    Wolfmeister (2/7/2011)

    If my understanding is correct then this method uses a tally table in conjunction with the SUBSTRING function to get the starting positions of each character in @string, and prefix each character with a comma. FOR XML PATH then concatenates the values into a single string, and finally the STUFF function then replaces the first comma with ''.

    Fantastic Mr.Wolf.. Thats exactly what the code does..

  • February 7, 2011 at 9:24 am

    #1283219

    Wolfmeister (2/7/2011)

    If my understanding is correct then this method uses a tally table in conjunction with the SUBSTRING function to get the starting positions of each character in @string, and prefix each character with a comma. FOR XML PATH then concatenates the values into a single string, and finally the STUFF function then replaces the first comma with ''.

    Absolutely correct. I just wanted to make sure that someone wasn't going to use a "black box" without understanding what it does because they'll need to support it in the future. Thanks for taking the time to write the feedback. 🙂

    --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)

  • February 7, 2011 at 9:31 am

    #1283233

    No problem, largely thanks to your article on tally tables which I read just last week 🙂

  • February 7, 2011 at 3:32 pm

    #1283469

    Wolfmeister (2/7/2011)

    No problem, largely thanks to your article on tally tables which I read just last week 🙂

    🙂

    --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)

  • February 8, 2011 at 2:06 am

    #1283569

    DECLARE @Sample VARCHAR(100) = '12345'

    ;WITH cte(CurrPos, LastPos, String)

    AS (

    SELECT1 AS CurrPos,

    DATALENGTH(@Sample) AS LastPos,

    CAST(@Sample AS VARCHAR(MAX)) AS String

    UNION ALL

    SELECTCurrPos + 1 AS CurrPos,

    LastPos,

    STUFF(String, 2 * CurrPos, 0, ',') AS String

    FROMcte

    WHERECurrPos < LastPos

    )

    SELECTString

    FROMcte

    WHERECurrPos = LastPos

    N 56°04'39.16"
    E 12°55'05.25"

  • February 8, 2011 at 7:17 am

    #1283660

    SwePeso (2/8/2011)

    DECLARE @Sample VARCHAR(100) = '12345'

    ;WITH cte(CurrPos, LastPos, String)

    AS (

    SELECT1 AS CurrPos,

    DATALENGTH(@Sample) AS LastPos,

    CAST(@Sample AS VARCHAR(MAX)) AS String

    UNION ALL

    SELECTCurrPos + 1 AS CurrPos,

    LastPos,

    STUFF(String, 2 * CurrPos, 0, ',') AS String

    FROMcte

    WHERECurrPos < LastPos

    )

    SELECTString

    FROMcte

    WHERECurrPos = LastPos

    Thanks, SwePeso, that's pretty neat as well:-)

Viewing 15 posts - 1 through 15 (of 25 total)

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