Split string using XML

Question

Split string using XML

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

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

rafidheenm SSC Veteran Points: 240 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

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.

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

sam.walker Mr or Mrs. 500 Points: 597 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

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.

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