Using REVERSE

  • I am trying to use the REVERSE function to "split" the field TreeMap into columns.  The function works great if the string only has 3 commas in in.  As you will see what I mean by using the attached SQL script.

    How can I get the REVERSE function to work with the rows that are not "splitting" correctly or is there a different function that I can use to get the results I am looking for.

    Any help is greatly appreciated.

  • Here is the code of the script.

     

    create table #ACME (TreePath nvarchar(max))

    insert into #ACME(TreePath)
    values('ACME products,Outdoor Equipment,Outdoor Accessories,Outdoor Accessories Shelter')
    insert into #ACME(TreePath)
    values('ACME products,Outdoor Equipment,Outdoor Accessories,Outdoor Accessories Skin Care')
    insert into #ACME(TreePath)
    values('ACME products,Outdoor Equipment,Outdoor Accessories,Outdoor Accessories Sleeping Gear')
    insert into #ACME(TreePath)
    values('ACME products,Outdoor Equipment,Outdoor Accessories,Outdoor Accessories Stoves')
    insert into #ACME(TreePath)
    values('ACME products,Outdoor Equipment,Outdoor Accessories,Outdoor Accessories Water Filtration/Treatment')
    insert into #ACME(TreePath)
    values('ACME products,Outdoor Equipment,Outdoor Accessories,Outdoor Accessories Water Filtration/Treatment')
    insert into #ACME(TreePath)
    values('ACME products,Outdoor Equipment,Outdoor Accessories,Outdoor Accessories Water Sport Gear')
    insert into #ACME(TreePath)
    values('ACME products,Outdoor Equipment,Outdoor Accessories,Outdoor Accessories Stoves,Outdoor Accessories Stoves DG')
    insert into #ACME(TreePath)
    values('ACME products,Outdoor Equipment,Outdoor Accessories,Outdoor Accessories Skin Care,Outdoor Accessories Skin Care DG')
    insert into #ACME(TreePath)
    values('ACME products,Outdoor Equipment,Outdoor Accessories,Outdoor Accessories Camp Kitchen,Outdoor Accessories Camp Kitchen DG')
    insert into #ACME(TreePath)
    values('ACME products,Bicycles,Bikes,Frames Mountain,Hardtail Mountain Frames,Cross Country Hardtail Frame')
    insert into #ACME(TreePath)
    values('ACME products,Bicycles,Bikes,Frames Mountain,Hardtail Mountain Frames,Fat Bike Hardtail Frame')
    insert into #ACME(TreePath)
    values('ACME products,Bicycles,Bikes,Frames Mountain,Hardtail Mountain Frames,Dirt Jump Hardtail Frame,Dirt Jump Hardtail Frame DG')

    select *
    , len(TreePath) - len(replace(TreePath,',','')) NumberofCommas
    ,REVERSE(PARSENAME(REPLACE(REVERSE(TreePath), ',', '.'), 1)) AS Level1
    ,REVERSE(PARSENAME(REPLACE(REVERSE(TreePath), ',', '.'), 2)) AS Level2
    ,REVERSE(PARSENAME(REPLACE(REVERSE(TreePath), ',', '.'), 3)) AS Level3
    ,REVERSE(PARSENAME(REPLACE(REVERSE(TreePath), ',', '.'), 4)) AS Level4
    ,REVERSE(PARSENAME(REPLACE(REVERSE(TreePath), ',', '.'), 5)) AS Level5


    from #ACME

    drop table #ACME
  • Since you are using SQL Server 2019, would "string_split()" function suit your needs?

    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

     

  • Mr. Brian Gale wrote:

    Since you are using SQL Server 2019, would "string_split()" function suit your needs?

    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

    The trouble with STRING_SPLIT() is that it does not return the ordinal position of the elements that have been split out and MS makes no guarantee as to the order.  You need to use something like DelimitedSplit8K for this.

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • @sqldummy79 ,

    No matter what we use, the test table is incomplete.  Each row needs to have something that uniquely identifies each row as it appears in the original source table.  Can you provide the example data with whatever that column is along with the TreePath column?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • In the mean time, here's the code to do the given problem with the given data.  There are some caveats, though...

       WITH 
    cteEnumerate AS (SELECT RowNum = ROW_NUMBER() OVER (ORDER BY @@SPID),TreePath FROM #ACME)
    ,cteSplit AS (--Split the data for each row and the ordinal number for each element)
    SELECT RowNum, split.ItemNumber, split.Item
    FROM cteEnumerate
    CROSS APPLY dbo.DelimitedSplit8K(TreePath,',') split
    )
    SELECT RowNum
    ,Level1 = MAX(CASE WHEN ItemNumber = 1 THEN Item ELSE NULL END)
    ,Level2 = MAX(CASE WHEN ItemNumber = 2 THEN Item ELSE NULL END)
    ,Level3 = MAX(CASE WHEN ItemNumber = 3 THEN Item ELSE NULL END)
    ,Level4 = MAX(CASE WHEN ItemNumber = 4 THEN Item ELSE NULL END)
    ,Level5 = MAX(CASE WHEN ItemNumber = 5 THEN Item ELSE NULL END)
    FROM cteSplit
    GROUP BY RowNum
    ;

    Caveats:

    1. The code uses a function called dbo.DelimitedSplit8K.  After incorporating a wonderful change (as of 2012) by Eirikur Eiriksson (his article located at https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2  ) that uses LEAD, it's the worlds fastest TSQL-Only string splitter.  I've attached the T-SQL code for the function as a .txt file because (I find it extremely odd on an SQL Server forum) this forum does not allow files with a .sql extension.
    2. This rendition ONLY handles VARCHAR(8000) and changing it to handle the MAX datatypes will make it run 3 times slower (although, as someone else once said about it all,  3 times slower than greased lightning might suffice 😀 ).
    3. It does not handle NVARCHAR() yet... it's on my list of things to do but it also looks like you don't have anything in TreePath that would actually require NVARCHAR().  Use with caution especially when it comes to datatype mismatches if using the output for joins.
    4. And, it goes without saying, we still need to know what the PK is from the original table.  That would best be done by you modifying the readily consumable test data that you so nicely provided.

    If you really need one that handles NVARCHAR(), I can make the change in a day or two.  I strongly recommend NOT using a MAX() datatype for the TreePath string.  In fact, I also strongly recommend against using a string for a TreePath (also known as a "Hierarchical Path") at all.  Please see the following two articles on the subject of some high performance hierarchies and conversions.

    https://www.sqlservercentral.com/articles/hierarchies-on-steroids-1-convert-an-adjacency-list-to-nested-sets

    https://www.sqlservercentral.com/articles/hierarchies-on-steroids-2-a-replacement-for-nested-sets-calculations-1

    Attachments:
    You must be logged in to view attached files.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Unfortunately, I can not use DelimitedSplit8K as I can not add functions on the server and I keep getting "'string_split' is not a recognized built-in function name." when I try to use.  Interesting thing is I am on SQL Server 2016 SP2 which should have the function and the database is set to Compatibility level:  SQL Server 2016 (130)

    Also, from my research on both of those ideas, it will take the string and put the results on individual rows, which is not what I need.  In the script that I provided it shows how I need the split data into columns not rows.

  • Jeff,

    Thanks for the script to create the function but unfortunately I can not add it onto the server

    Each row does have a PK, I just didn't add it to the script.  Could you add it to the script, if that will help with figuring out what I need.

  • sqldummy79 wrote:

    Unfortunately, I can not use DelimitedSplit8K as I can not add functions on the server and I keep getting "'string_split' is not a recognized built-in function name." when I try to use.  Interesting thing is I am on SQL Server 2016 SP2 which should have the function and the database is set to Compatibility level:  SQL Server 2016 (130)

    Wow.  Nothing like them hamstringing the system.  Someone needs to give them a clue about iTVFs (inline Table Valued Functions).  They have none of the problems that either scalar UDFs or mTVFs (multi-statement Table Valued Functions) provide and add a huge amount of consistency and some fairly incredible functionality to databases.

    sqldummy79 wrote:

    Also, from my research on both of those ideas, it will take the string and put the results on individual rows, which is not what I need.  In the script that I provided it shows how I need the split data into columns not rows.

    The code I wrote that uses DelimitedSplit8K solves that issue using an ancient "Black Arts" method known as a "CROSSTAB" in the final SELECT of the code.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • p.s. Like I said, STRING_SPLIT() isn't going to do the job correctly because it doesn't return the ordinal position of the elements nor does MS guarantee the sort order it returns.

    We can still solve this but you've not answered the overarching question of what is the PK of the table that you're getting the TreePath column from?

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Sorry... missed your previous post about the PK.

    I have one in the code I posted using ROW_NUMBER() but that's not going to help you.  Help me help you by adding the proper data to the readily consumable table/data that  you posted.  I need actual examples of what you're using for the PK on the table that TreePath comes from.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • We can brute force this using CHARINDEX:

    Declare @pDelimiter char(1) = ',';

    Select a.TreePath
    , Level1 = ltrim(substring(v.treePath, 1, p01.pos - 2))
    , Level2 = ltrim(substring(v.treePath, p01.pos, p02.pos - p01.pos - 1))
    , Level3 = ltrim(substring(v.treePath, p02.pos, p03.pos - p02.pos - 1))
    , Level4 = ltrim(substring(v.treePath, p03.pos, p04.pos - p03.pos - 1))
    , Level5 = ltrim(substring(v.treePath, p04.pos, p05.pos - p04.pos - 1))
    , Level6 = ltrim(substring(v.treePath, p05.pos, p06.pos - p05.pos - 1))
    From #ACME As a
    Cross Apply (Values (concat(a.TreePath, replicate(@pDelimiter, 6)))) As v(treePath)
    Cross Apply (Values (charindex(@pDelimiter, v.treePath, 1) + 1)) As p01(pos)
    Cross Apply (Values (charindex(@pDelimiter, v.treePath, p01.pos) + 1)) As p02(pos)
    Cross Apply (Values (charindex(@pDelimiter, v.treePath, p02.pos) + 1)) As p03(pos)
    Cross Apply (Values (charindex(@pDelimiter, v.treePath, p03.pos) + 1)) As p04(pos)
    Cross Apply (Values (charindex(@pDelimiter, v.treePath, p04.pos) + 1)) As p05(pos)
    Cross Apply (Values (charindex(@pDelimiter, v.treePath, p05.pos) + 1)) As p06(pos);

    As for why STRING_SPLIT isn't recognized, check the compatibility level on the database.  If you have more positions - just add additional CROSS APPLY and columns and increase the number of @pDelimiter in the replicate.  That insures that all strings can be parsed even if they don't have any delimiters included.

    • This reply was modified 1 week, 5 days ago by  Jeffrey Williams. Reason: Added Level6 to the code

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • First, you should encode the string values to a numeric equivalent.  Imagine what must happen now when the name of a level changes.  For example, say that "Outdoor Accessories" had to change to "Outdoor Accessories and Widgets".  What a nightmare having to change multiple entries on the same line for multiple rows.  Gack!

    So, 'ACME products' might become 1, 'Outdoor Equipment' = 2, etc..  Then the strings would be more like:

    '1,2,5,7,9'

    If you need to search for the actual descriptions, you can do that far more efficiently from a normalized with all the unique descriptions than from a table with endless repeats of all the descriptions in row after row.

    You can, however, make the existing table name a view that looks like your original table and use another name for the actual underlying table that now contains only encoded values.

    I suspect you'll ignore this big time.  True, it might be a big change to implement.  But it will only get harder later.  And it would save you tons of issues in the future.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • You don't really need a pk or any other column from the main table.  Also, I assumed you'd rather see blank than NULL for missing levels, but if not, just remove the ISNULL()s and naturally you'll see NULLs instead of blanks.

    SELECT ca1.*
    FROM #ACME A
    CROSS APPLY (
    SELECT
    ISNULL(MAX(CASE WHEN ItemNumber = 1 THEN Item END), '') AS Level1,
    ISNULL(MAX(CASE WHEN ItemNumber = 2 THEN Item END), '') AS Level2,
    ISNULL(MAX(CASE WHEN ItemNumber = 3 THEN Item END), '') AS Level3,
    ISNULL(MAX(CASE WHEN ItemNumber = 4 THEN Item END), '') AS Level4,
    ISNULL(MAX(CASE WHEN ItemNumber = 5 THEN Item END), '') AS Level5,
    ISNULL(MAX(CASE WHEN ItemNumber = 6 THEN Item END), '') AS Level6,
    ISNULL(MAX(CASE WHEN ItemNumber = 7 THEN Item END), '') AS Level7,
    ISNULL(MAX(CASE WHEN ItemNumber = 8 THEN Item END), '') AS Level8,
    ISNULL(MAX(CASE WHEN ItemNumber = 9 THEN Item END), '') AS Level9,
    ISNULL(MAX(CASE WHEN ItemNumber =10 THEN Item END), '') AS Level10
    FROM ( SELECT * FROM dbo.DelimitedSplit8K(A.TreePath, ',') ) AS ds
    ) AS ca1

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher wrote:

    You don't really need a pk or any other column from the main table.  Also, I assumed you're rather see blank than NULL for missing levels, but if not, just remove the ISNULL()s and naturally you'll see NULLs instead of blanks.

    SELECT ca1.*
    FROM #ACME A
    CROSS APPLY (
    SELECT
    ISNULL(MAX(CASE WHEN ItemNumber = 1 THEN Item END), '') AS Level1,
    ISNULL(MAX(CASE WHEN ItemNumber = 2 THEN Item END), '') AS Level2,
    ISNULL(MAX(CASE WHEN ItemNumber = 3 THEN Item END), '') AS Level3,
    ISNULL(MAX(CASE WHEN ItemNumber = 4 THEN Item END), '') AS Level4,
    ISNULL(MAX(CASE WHEN ItemNumber = 5 THEN Item END), '') AS Level5,
    ISNULL(MAX(CASE WHEN ItemNumber = 6 THEN Item END), '') AS Level6,
    ISNULL(MAX(CASE WHEN ItemNumber = 7 THEN Item END), '') AS Level7,
    ISNULL(MAX(CASE WHEN ItemNumber = 8 THEN Item END), '') AS Level8,
    ISNULL(MAX(CASE WHEN ItemNumber = 9 THEN Item END), '') AS Level9,
    ISNULL(MAX(CASE WHEN ItemNumber =10 THEN Item END), '') AS Level10
    FROM ( SELECT * FROM dbo.DelimitedSplit8K(A.TreePath, ',') ) AS ds
    ) AS ca1

    Yes, that'll work... until you need to join that back to information in the "real" table.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 15 posts - 1 through 15 (of 23 total)

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