Tally Table to split string

Question

Tally Table to split string

eseosaoregie

SSCertifiable

Points: 6889
More actions
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

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

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

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 1

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(

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

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 2

eseosaoregie

SSCertifiable

Points: 6889

May 11, 2010 at 12:02 am

#1165348

Thanks. I wil give this a go.

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 3

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)

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 5

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)