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
Change is inevitable... Change for the better is not.