February 21, 2022 at 4:19 pm
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:
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?
February 21, 2022 at 4:58 pm
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;
February 21, 2022 at 5:05 pm
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 + '%'
February 21, 2022 at 5:13 pm
Thanks Kaj...that worked very well for me
February 21, 2022 at 5:13 pm
Hi Ratnbak
no there shouldnt be a space after the comma...that was a typo!
February 21, 2022 at 9:05 pm
[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
Change is inevitable... Change for the better is not.
February 22, 2022 at 11:26 am
Hi jeff
Thats a very elegant solution .. thank you very much for that
February 23, 2022 at 2:01 am
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy