Home Forums Article Discussions Article Discussions by Author Discuss content posted by bevan ward Set-Based String Splitting table function RE: Set-Based String Splitting table function

  • March 22, 2018 at 2:19 pm

    #1984353

    Eirikur Eiriksson - Thursday, March 22, 2018 4:14 AM

    Hi Guys,

    and thanks for the nice comments, wouldn't want to take too much credit for this as it is a community (SSC or Sql Server Community) effort.

    I got some suggestions:

    1. The (n)varchar(max) can hold up to (2^31) - 1 or 2147483647 characters, the tally table must be able to produce that number, optimal construct would be using 15^8 or something like

    WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
    , NUMS(N) AS (SELECT 0 UNION ALL
         SELECT TOP (DATALENGTH(ISNULL(@pString,1))/2) ROW_NUMBER() OVER (ORDER BY @@VERSION)
         FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8)

    2. Consider using a single character token as a delimiter and replace the multi character delimiter with the token before passing it to the function. This is straight forward as one can encapsulate the single character token function in another function.

    3. One of the reasons for the LEAD/LAG versions performing so well is that they utilize the special worktable, but it has limits. If the number of rows exceed that limit, then the benefits are minimal or even negative.

    😎

    I've done some extensive work on this and even have an article draft with code and a test harness somewhere. Will try to find it and post back when I have the chance.


     

    Hi Eirikur

    Thanks for this - most appreciated.

    I'll update the tally table to your new method - the order by @@version is something I haven't seen before but am sure is better then order by (select null).

    The replacement prior to running a singe delimiter makes a lot of sense as it will stop the need for testing for overlapping delimiters too.

    The way I use it means that I'm not sure on the data size so I'll have to start doing some more complete testing once I've made these changes.

    Thanks again - the way you have collated, documented and enhanced the community work is excellent and appreciated by many!
    Bevan