Performing String Split on items which have been String Split
-
Hi there
I have the following string which is comma seperated but within each item, they are seperated by '_' as follows:
DECLARE @TestString VARCHAR(MAX) = N'B0AF47E9-E161-4EAD-A690-99C06E80A4B4_11, 441D9F5D-D4AA-416F-8F09-F70DE5D36644_12'
Now what i want to do is the following:
- Split items out of the comma seperated string
- Then parse each item and split each item which contains the underscore
So I would have the following end result
how would i do this please?
I tried the following:
DROP TABLE IF EXISTS [#Items]
DECLARE @TestString VARCHAR(MAX) = N'B0AF47E9-E161-4EAD-A690-99C06E80A4B4_11, 441D9F5D-D4AA-416F-8F09-F70DE5D36644_12'
CREATE TABLE [#Items](id int identity(1,1) Not null, [Item] nvarchar(max))
Insert into [#Items] ([Item])
select value from string_split(@TestString,',')
That gives me the items from the original string split into rows
but I then need to manipulated it to get this
im thinking that i need to do a string spliut and then pivot on the result?
-
There are of course several ways to do this, but since the format is pretty fixed, you could use this:
DECLARE @TestString VARCHAR(MAX) = N'B0AF47E9-E161-4EAD-A690-99C06E80A4B4_11, 441D9F5D-D4AA-416F-8F09-F70DE5D36644_12';
With sp_data as (
select value as rowvalue from string_split(@TestString,',')
)
select
left(rowvalue,charindex('_',rowvalue)-1) as DeviceID,
substring(rowvalue,charindex('_',rowvalue)+1,255) as Channel
from sp_data; -
Do you really have a space after the comma in the strings? If so, you'll need to trim that.
This will handle the re-split (assuming the strings are consistent... Is the string always just two values split by an underscore?), though an approach w/ charindex/left/right functions might be more efficient:
WITH splitStrings AS
(
SELECT LTRIM(value) AS partiallySplit from string_split(@TestString,',')
)
SELECT splitStrings.partiallySplit, resplit.value
FROM splitStrings
CROSS APPLY STRING_SPLIT(splitStrings.partiallySplit,'_') resplit
WHERE partiallySplit NOT LIKE value + '%' -
Thanks Kaj...that worked very well for me
-
Hi Ratnbak
no there shouldnt be a space after the comma...that was a typo!
-
[EDIT] I just noticed that this is almost identical to @kaj's good code.
This should help and it will also help prevent mistakes with spaces (like ratbak also helped prevent in their code)... it's a solution that does the split, creates the temp table with the correct data types,and populates it all in one "Fell Swoop".
--===== If it exists, drop the temp table just to make reruns in SSMS easier.
DROP TABLE IF EXISTS #Items
;
--===== The data given by the OP
DECLARE @TestString VARCHAR(MAX) = N'B0AF47E9-E161-4EAD-A690-99C06E80A4B4_11, 441D9F5D-D4AA-416F-8F09-F70DE5D36644_12'
;
--===== A solution that does the split, creates the temp table with the correct data types,
-- and populates it all in one "Fell Swoop".
WITH cte AS
(SELECT SplitValue = LTRIM(RTRIM(value)) FROM STRING_SPLIT(@TestString,','))
SELECT RowNum = IDENTITY(INT,1,1) --Note that SPLIT_STRING DOES NOT GUARANTEE THE CORRECT ORDER!!!
,DeviceID = CONVERT(UNIQUEIDENTIFIER,SUBSTRING(cte.SplitValue,1,CHARINDEX('_',cte.SplitValue)-1))
,Channel = CONVERT(INT,SUBSTRING(cte.SplitValue,CHARINDEX('_',cte.SplitValue)+1,10))
INTO #Items
FROM cte
;
--===== Display the result
SELECT * FROM #Items ORDER BY RowNum
;Results:
Also, when you're posting code, please click on the outlined item to open a code window and paste your code there for future posts.
--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) -
Hi jeff
Thats a very elegant solution .. thank you very much for that
-
Thank you for the feedback. Just remember that the STRING_SPLIT() function does not currently guarantee any order in any version of on-prem SQL Server.. They finally figured that out and made it so it returns (in Azure only) an ordinal position like the DelimitedSplit8K community-built splitter that many of us use.
--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)
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply