Thanks for taking the time to write this up. Interesting - you've arrived at a few of the shortcuts of the house splitter
http://www.sqlservercentral.com/articles/Tally+Table/72993/ written by Jeff Moden (and others).
I would imaging that storing the results into a table variable then querying that for the output will cost some time, is it really necessary?
Yes, this is the gold standard for string splitting in pure SQL. Surprised the author was not aware of it
While I've seen (and am using) Jeff's approach noted above (thanks Jeff!), I'm not going to ding @bevanward for not being aware of this previous article.
I say kudos to bevan for posting an approach that, while perhaps not as comprehensive as Jeff's, was clearly arrived at independently of Jeff's work. And just taking the time to share some ideas is a cool thing.
Hi Scott
Thanks for your message! It is hard to know how long to look for something before starting to write. What Jeff wrote looks perfect.
Had you seen the modernised version that Jeff mentions above? Seems to be worth considering from the looks of the performance.
Thanks again keep well
Bevan