Performing String Split on items which have been String Split

Question

Performing String Split on items which have been String Split

Weegee71

SSChasing Mays

Points: 604
More actions
February 21, 2022 at 4:19 pm

#3986478
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:
1. Split items out of the comma seperated string
2. 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?

Viewing 8 posts - 1 through 7 (of 7 total)

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

kaj SSCommitted Points: 1607 More actions · Answer 1

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;

ratbak Ten Centuries Points: 1382 More actions · Answer 2

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 + '%'

Weegee71 SSChasing Mays Points: 604 More actions · Answer 3

Weegee71

SSChasing Mays

Points: 604

February 21, 2022 at 5:13 pm

#3986493

Thanks Kaj...that worked very well for me

Weegee71 SSChasing Mays Points: 604 More actions · Answer 4

Hi Ratnbak

no there shouldnt be a space after the comma...that was a typo!

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

[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.

This reply was modified 2 years, 10 months ago by Jeff Moden. Reason: I just noticed that this is almost identical to @kaj's good 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.

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

Weegee71 SSChasing Mays Points: 604 More actions · Answer 6

Hi jeff

Thats a very elegant solution .. thank you very much for that

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

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)