Post reply

Tally Table to split string

  • May 10, 2010 at 4:27 pm

    #156939

    I came across an article on the forum written by Jeff Moden about using tally tables to split delimited. I have implemented this in numerous instances and found the benefits in terms of performance.

    Is it possible to extend the use of the tally table to split strings which contain different delimiters?

    eg

    split '456|wewed,ewewewe,wewewfrfr;f123221;wewewewe/eererewrwe/'

    to

    456 wewed ewewewe wewewfrfr f123221 wewewewe eererewrwe

  • May 10, 2010 at 6:20 pm

    #1165282

    eseosaoregie (5/10/2010)

    I came across an article on the forum written by Jeff Moden about using tally tables to split delimited. I have implemented this in numerous instances and found the benefits in terms of performance.

    Is it possible to extend the use of the tally table to split strings which contain different delimiters?

    eg

    split '456|wewed,ewewewe,wewewfrfr;f123221;wewewewe/eererewrwe/'

    to

    456 wewed ewewewe wewewfrfr f123221 wewewewe eererewrwe

    Yep... you can simply do a series of nested replaces (very fast, by the way) to make all the delimiters the same. For example...

    SELECT REPLACE(

    REPLACE(

    REPLACE(

    '456|wewed,ewewewe,wewewfrfr;f123221;wewewewe/eererewrwe/'

    ,'|',',')

    ,';',',')

    ,'/',',')

    --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)
    Intro to Tally Tables and Functions

  • May 11, 2010 at 12:02 am

    #1165348

    Thanks. I wil give this a go.

  • May 11, 2010 at 6:25 am

    #1165489

    You bet... thank you for 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)
    Intro to Tally Tables and Functions

  • May 18, 2010 at 5:58 am

    #1168737

    That seemed to work. I just had to add a PIVOT clause to reformat the data.

  • May 18, 2010 at 7:23 am

    #1168801

    Very cool. Thanks for the time to make a feedback. If you get the chance, post your code so folks can see what you've done with PIVOT.

    --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)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

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