String to rows

  • Iulian -207023

    SSCertifiable

    Points: 7508

    Hello,

    How can I parse a string into rows?

    Ex: from_ text = "A # B # C"

    I need to get_:

    Col_Name

    ------------

    A

    B

    C

    Thank you,

    Iulian

  • Jason-299789

    SSC-Insane

    Points: 21601

    The code sample in this link should help. http://www.codeproject.com/KB/database/splitparameterstring.aspx%5B/url%5D

    The only difference is that instead of using a comma seperated list you use a # delimiter.

    One minor tweak could be to change the function to accept a Delimiter so that its configurable depending on your needs.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8336

    Try:

    DECLARE @xml xml, @v nvarchar(max)

    SELECT @v = N'A # B # C'

    SET @xml = N'<root><r>' + replace(@v,'#','</r><r>') + '</r></root>'

    SELECT

    replace(a.value('.','varchar(2)'),' ','') AS [RowName]

    FROM @xml.nodes('//root/r') AS a(a)

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • ghanshyam.kundu

    Hall of Fame

    Points: 3337

    thanks carolyn, its really a nice solution i use to do it via UDF.

    i think in term of performance also this one is better.

    thanks

    ghanshyam

  • Sean Lange

    SSC Guru

    Points: 286515

    Jason-299789 (10/7/2011)


    The code sample in this link should help. http://www.codeproject.com/KB/database/splitparameterstring.aspx%5B/url%5D

    The only difference is that instead of using a comma seperated list you use a # delimiter.

    One minor tweak could be to change the function to accept a Delimiter so that its configurable depending on your needs.

    Instead of using a loop based solution you should instead use a set based solution for this. Our very won Jeff Moden has a fantastic delimited string splitter. You can fit it here. http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]. The link you pointed to is the more typical loop based approach. It works ok on small datasets but is totally crippled with large datasets.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Iulian -207023

    SSCertifiable

    Points: 7508

    Thank you so much to all of you

    I think I will try both methods

    the xml method to parse the file into lines and push to a table

    then the CLR method eith regular expressions to parse each line into fields

    Kind regards,

    Iulian

  • SQLRNNR

    SSC Guru

    Points: 281243

    Faster would be to use the Split function by Jeff Moden already mentioned or to use a CLR splitter that you can find in the discussion on the link provided by Sean.

    If you need to break those rows into columns, why not use the pivot command?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • yubo1

    SSChasing Mays

    Points: 612

    create table test

    (

    string varchar(max)

    )

    insert into test

    select 'A#B#C'

    select SUBSTRING(string+'#',number,charindex('#',string+'#',number)-number) from

    test,

    (select number from master..spt_values where number>=1 and type='P')t

    where SUBSTRING('#'+string,number,1)='#'

  • Jeff Moden

    SSC Guru

    Points: 995648

    yubo1 (10/8/2011)


    create table test

    (

    string varchar(max)

    )

    insert into test

    select 'A#B#C'

    select SUBSTRING(string+'#',number,charindex('#',string+'#',number)-number) from

    test,

    (select number from master..spt_values where number>=1 and type='P')t

    where SUBSTRING('#'+string,number,1)='#'

    Yubo1,

    That method is the "old" method of splitting that uses concatenated delimiters. It's very fast for a small number of elements in the string but rapidly degrades with string length. Please see the following article for why you shouldn't use the method you posted and a much faster (closest yet to a decent CLR) methods as an alternative.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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 Moden

    SSC Guru

    Points: 995648

    Iulian -207023 (10/7/2011)


    Thank you so much to all of you

    I think I will try both methods

    the xml method to parse the file into lines and push to a table

    then the CLR method eith regular expressions to parse each line into fields

    Kind regards,

    Iulian

    Nope... I recommend that don't use the XML method... it's almost as slow as a While Loop. Please see the following article for proof of that and the recommended "new" alternative.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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 Moden

    SSC Guru

    Points: 995648

    Carolyn Richardson (10/7/2011)


    Try:

    DECLARE @xml xml, @v nvarchar(max)

    SELECT @v = N'A # B # C'

    SET @xml = N'<root><r>' + replace(@v,'#','</r><r>') + '</r></root>'

    SELECT

    replace(a.value('.','varchar(2)'),' ','') AS [RowName]

    FROM @xml.nodes('//root/r') AS a(a)

    Hi Carolyn,

    Please see the following article for the reasons why I recommend you don't use the XML method of splitting and the relatively new method of splitting whose speed is rivaled only by a well written CLR.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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 Moden

    SSC Guru

    Points: 995648

    Jason-299789 (10/7/2011)


    The code sample in this link should help. http://www.codeproject.com/KB/database/splitparameterstring.aspx%5B/url%5D

    The only difference is that instead of using a comma seperated list you use a # delimiter.

    One minor tweak could be to change the function to accept a Delimiter so that its configurable depending on your needs.

    Jason,

    The method in that article is what is known as a "Nibbler". Please see the following article for why that method probably shouldn't be used and what the new, high performance method of splitting is...

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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 Moden

    SSC Guru

    Points: 995648

    ghanshyam.kundu (10/7/2011)


    thanks carolyn, its really a nice solution i use to do it via UDF.

    i think in term of performance also this one is better.

    thanks

    ghanshyam

    But, it's not. Please see the following article for a relatively new, high speed splitter that uses an iTVF (Inline Table Valued Function) instead of a relatively slow mTVF (Multi-Line Table Valued Function).

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • Iulian -207023

    SSCertifiable

    Points: 7508

    Thank you all,

    Do you have at hand a sample code on how to create the CLR function for splitting a string into rows / fields? I am not familiar with CLR functions, yet 🙂

    I need to parse each line using regular expression, at least this is what I have in mind now.

    After reading your recommendations I have the feeling regular expression using CLR is the key of my exercise. I appreciate any help / sample on this.

    A sample:

    If the input is:

    A # 2 # 09/20/2011

    X # 09/20/2011 & 123.00

    A # 3 # 10/07/2011

    the result will have 3 rows, each row having 4 fields:

    Record_Name ID Date Value

    A 2 09/20/2011 0

    X 0 09/20/2011 123.00

    A 3 10/07/2011 0

    Thanks,

    Iulian

  • Jeff Moden

    SSC Guru

    Points: 995648

    Iulian -207023 (10/8/2011)


    Thank you all,

    Do you have at hand a sample code on how to create the CLR function for splitting a string into rows / fields? I am not familiar with CLR functions, yet 🙂

    I need to parse each line using regular expression, at least this is what I have in mind now.

    After reading your recommendations I have the feeling regular expression using CLR is the key of my exercise. I appreciate any help / sample on this.

    A sample:

    If the input is:

    A # 2 # 09/20/2011

    X # 09/20/2011 & 123.00

    A # 3 # 10/07/2011

    the result will have 3 rows, each row having 4 fields:

    Record_Name ID Date Value

    A 2 09/20/2011 0

    X 0 09/20/2011 123.00

    A 3 10/07/2011 0

    Thanks,

    Iulian

    You need to read the code attached to the article I pointed you to... it's in there. And, no... using regular expressions for split probably won't be faster than a CLR written specifically to do splits.

    Personally, I think enabling CLR just for the sake of a splitter is overkill. If it's already enabled, the the CLR splitter code I've just pointed you to will do the job nicely. You do have to make up your mind what you want to use for a delimiter, though. 😉

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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 15 posts - 1 through 15 (of 20 total)

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