Split string in one column multiple delimiters to table maintaining ID

Question

I would like to split a string into a new table that I can use to join to the original table. See code for an idea how data is stored in a column. I've

CREATE TABLE #tmpsplit
(
id INT IDENTITY(1,1) 
, StringToSplit varchar(100) 
)
INSERT INTO #tmpsplit VALUES ('Lable1:Value1|Lable2:Value2')
INSERT INTO #tmpsplit VALUES ('Lable1:ValueOne|Lable2:ValueTwo|Lable3:ValueThree')

I would very much like this to be split in three columns, ID, Lable, Value like so:

id  lable      value

1   Lable1     Value1
1   Lable2     Value2
2   LableOneV  ValueOne
2   LableTwo   ValueTwo
2   LableThree ValueThree

Preferably I'd like to do this without cursors, without variables using ths XML trick. I've come up with this (I cannot figure out how to add the ID per record).

SELECT
  SUBSTRING(t2.value,1,CHARINDEX(':',t2.value)-1) AS Lable 
, SUBSTRING(t2.value,CHARINDEX(':',t2.value)+1,LEN(t2.value)) AS Value
FROM
(SELECT Cast ('<x>' + Replace(StringToSplit, '|', '</x><x>') + '</x>' AS XML) AS RawData FROM #tmpsplit) t1
CROSS APPLY
(SELECT y.value('.','varchar(100)') as value FROM RawData.nodes('x') as f(y)) t2

Thank you.

You could do this accordingly to this post [split comma separated string into columns](https://stackoverflow.com/questions/50383351/split-comma-separated-string-into-columns). — Michał Turczyn, Aug 24 '18 at 06:53

score 2 · Accepted Answer · answered Aug 24 '18 at 06:36

2

just include the id field in your subquery

SELECT
id
,SUBSTRING(t2.value,1,CHARINDEX(':',t2.value)-1) AS Lable 
,SUBSTRING(t2.value,CHARINDEX(':',t2.value)+1,LEN(t2.value)) AS Value
FROM
(SELECT id, Cast ('<x>' + Replace(StringToSplit, '|', '</x><x>') + '</x>' AS XML) AS 
RawData FROM #tmpsplit) t1
CROSS APPLY
(SELECT y.value('.','varchar(100)') as value FROM RawData.nodes('x') as f(y)) t2

answered Aug 24 '18 at 06:36

linden.phoenix

194
5

Thank you. I have no idea why I didn't get this in the first place. I feel sheepish. – Jan-Willem Aug 24 '18 at 06:58

score 0 · Answer 2 · answered Aug 24 '18 at 07:08

You can use STRING_SPLIT function. this function available in SQL 2016 and it's above version. If you have smaller version then you can create table value split function and use that function in below query. Hope this will help you.

SELECT   
t.id
,SUBSTRING(t2.value,1,CHARINDEX(':',t2.value)-1) AS Lable 
, SUBSTRING(t2.value,CHARINDEX(':',t2.value)+1,LEN(t2.value)) AS Value
FROM #tmpsplit T
CROSS APPLY (SELECT value FROM STRING_SPLIT(T.StringToSplit, '|'))T2

Split string in one column multiple delimiters to table maintaining ID

2 Answers2