Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets RE: Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

  • November 30, 2012 at 10:22 am

    #1564486

    Lynn Pettis (11/29/2012)

    Jeff Moden (11/29/2012)

    Lynn Pettis (11/28/2012)

    Combined some of the code from your code in this article with your delimited split routine to solve a problem at work. Inspiration comes from many places.

    Have to give it to you Jeff, you are the Mentor of the Year in my book.

    :blush: Thanks, Lynn. I really appreciate that. What did you end up doing?

    Needed to sort DD Version Keys (x.x.x, like 2.1.10). The original code had no problems as it didn't have to work with a 2 digit value. When we went to 2.1.10 instead of 2.3.1, it was a problem. The original solution by another DBA used the OLE Automation Procedures to create a regex_replace function. Only problem is that there is a STIG requiring that these procedures be disabled.

    I'm sure there may be another solution, but combining the DelimitedSplit8K and the varbinary concatenation from this article I was able to generate a solution that correctly sorted the version keys, including a very unlikely set of numerical elements (x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n)) where n represents 1 or more digits.

    I wouldn't have thought of concatenating varbinary values had I not read this article.

    VERY cool. Thanks for posting the usage, Lynn.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)