Split string using XML

  • I have defined @split_string, SplitString (column) as varchar(8000). For 2005 or later users it can be varchar(max) (for too large string process)which will not affect the performance..

  • I know it's an old post but I thought I'd provide an update just in case anyone is still thinking about using XML for splitting. Please see the following article...

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

    If there were any doubt before, there isn't now. 🙂

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff,

    I maintain the fastest way of splitting a string is a combined process.

    1. Pass the string, delim, to clr function

    2. The clr function converts each item in to a fixed char width item, eg 20 chars per item.

    3. The returned string is split by an inline function querying a tally table using substring.

    I've tried all other methods and they are much slower.

    Pure clr is slow because it is slow to pass back so many records.

    Pure SQL is slow because it is slow at lookup and constructor functions.

    Jeff, if you want the exact code, happy to send it through.

  • sam.walker (5/9/2011)


    Jeff,

    I maintain the fastest way of splitting a string is a combined process.

    1. Pass the string, delim, to clr function

    2. The clr function converts each item in to a fixed char width item, eg 20 chars per item.

    3. The returned string is split by an inline function querying a tally table using substring.

    I've tried all other methods and they are much slower.

    Pure clr is slow because it is slow to pass back so many records.

    Pure SQL is slow because it is slow at lookup and constructor functions.

    Jeff, if you want the exact code, happy to send it through.

    I've not found CLR code to be slow for splitters when they're done properly. Please see the code at the article I posted and test yours against the code that's in there and then post your results here. There's also a standard test data setup for your tests. A simple modification of the code to include your method will allow the automatic running and reporting of your code and the other CLR for 1-10, 10-20, 20-30, 30-40, and 40-50 random length elements across an even wider range of number of elements. Why do I want you to do it? After that article, I'm a bit burned out on testing everyone else's code. 😀

    Also, what do you do when you have items that are 21 characters in length?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 4 posts - 46 through 49 (of 49 total)

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