April 6, 2021 at 7:40 pm
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.
April 6, 2021 at 7:41 pm
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
April 6, 2021 at 9:56 pm
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 above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 7, 2021 at 3:15 pm
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
Change is inevitable... Change for the better is not.
April 7, 2021 at 3:46 pm
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
Change is inevitable... Change for the better is not.
April 7, 2021 at 4:16 pm
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:
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2021 at 5:05 pm
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.
April 7, 2021 at 5:46 pm
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.
April 7, 2021 at 5:49 pm
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.
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
Change is inevitable... Change for the better is not.
April 7, 2021 at 5:52 pm
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
Change is inevitable... Change for the better is not.
April 7, 2021 at 5:55 pm
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
Change is inevitable... Change for the better is not.
April 7, 2021 at 6:37 pm
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.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 7, 2021 at 7:58 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 7, 2021 at 8:13 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 7, 2021 at 9:28 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply