Simple and efficient way to split string with seperator

  • Comments posted to this topic are about the item Simple and efficient way to split string with seperator

  • Very elegant solution, but I have just 2 concerns.

    One is that the definition of the string is varchar(8000), but the master table has values only up to 2047.

    The other I'm not sure if is really a problem, but with the lack of an order by you could get results out of order. Then again, maybe the onus of including an order by should be on the code calling this function.

    Anyway, I liked the solution. Thanks for posting.

  • Hi Filipe,

    Thank you very much for your comment.

    1) You are right, this is the limitation and the input string @string should be of type varchar(2047) instead of varchar(8000). If the string length is bigger than 2047, we will have to setup a number list table in advance. But the idea will be similar. Also this same idea can be used to efficiently split a string column of a table and the length of string from most tables are most likely less than 2047.

    2) I believe the "order by" statement is not needed since it will just follow the order of table master..spt_values. At least that is what I have been noticed so far.

  • I applaud the effort, except I have to ask why this function would be preferable to the following code:

    Jeff Moden's CSV splitter[/url]

    I have searched many resources, and it seems like this splitter function is pretty close to a "gold standard" for splitter functions using T-SQL.

    I would recommend that function to anyone seeking a fast and efficient way to split strings.

    Had you reviewed that article before writing yours?


    Past performance != future results.
    All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...

  • I did not know about the article you mentioned. I was trying to find a solution to split a string without looping and also want to split string from a large table. Even this funciton can be called with a string parameter, I find it is much more very useful and handy to use this idea to split a field from a table without using a splitting string function with looping. It is also more efficient this way.

  • Thanks for the script.

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

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