waxb18 (5/2/2014)
Thanks for the suggestion and the advice.
I have taken a look at the splitter and it works very well.
However the purpose of this exercise is for my own development, nothing will be implemented as a business solution, I'm just trying to find the best way to get the outcome.
I'm pretty confident that Jeff's DelimitedSplit8k is going to be the "best" way using T-SQL. "Best" being defined as the fastest way to return the correct results.
If i was to change the type of function to scalar, would this end up giving the correct dataset required?
I have also been looking at using a row_number function to define the data and then to iterate through each line, however i only return the final line (see below)
Changing the code to a scalar function would likely return the correct results, but do it very slowly as it will have to perform the loop through the delimited string for each row. It might give acceptable performance for 1000 rows, but wouldn't scale much beyond it. As Ed said, "There are, without a doubt, many ways to skin this cat", but Jeff's solution is the best way I've seen or used when implementing solely using T-SQL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question