Post reply

Simple and efficient way to split string with seperator

  • July 11, 2013 at 8:21 pm

    #277271

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

  • July 12, 2013 at 9:07 am

    #1631996

    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.

  • July 12, 2013 at 9:00 pm

    #1632167

    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.

  • July 29, 2013 at 2:26 pm

    #1636766

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

  • July 30, 2013 at 3:47 pm

    #1637154

    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.

  • May 2, 2016 at 1:04 pm

    #1876013

    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