String Split String With multiple columns same separator

Question

I have the following string

'06/30/2020;58044.373;.001;12/31/2020;58042.373;.003;06/30/2021;78044.373;.007'

I need to transform it into :

col 1       col 2         col 3
----        -----         -----
06/30/2020  58044.373     .001
12/31/2020  58042.373     .003
06/30/2021  78044.373     .007

I can use only select statmets, no cursors or SP's .

Any help please?

This is just one reason why you shouldn't store delimited data in your RDBMS. — Thom A
– Thom A ♦, Commented Dec 27, 2019 at 12:07

Lukasz Szozda · Accepted Answer · 2019-12-27 11:58:21Z

2

Workaround with STRING_SPLIT(it is not safe):

WITH cte AS (
  SELECT c, value,
      ROW_NUMBER() OVER(ORDER BY (SELECT 1)) % 3 AS rn,
      (ROW_NUMBER() OVER(ORDER BY (SELECT 1))-1) / 3 AS grp
  FROM t
  CROSS APPLY STRING_SPLIT(c, ';')
)
SELECT MAX(CASE WHEN rn = 1 THEN value END) AS col1,
       MAX(CASE WHEN rn = 2 THEN value END) AS col2,
       MAX(CASE WHEN rn = 0 THEN value END) AS col3
FROM cte
GROUP BY c,grp;

db<>fiddle demo

answered Dec 27, 2019 at 11:58

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Thom A Over a year ago

I never feel safe trusting STRING_SPLIT for stuff like this. The omission of the ordinal position is a huge oversight by Microsoft.

Lukasz Szozda Over a year ago

@Larnu Totally agree, WITH ordinality is a must- feedback.azure.com/forums/908035-sql-server/suggestions/…

Thom A Over a year ago

There's been a few of those topics since STRING_SPLIT was added back in 2016. I know I've voted up a couple at least.

Lukasz Szozda Over a year ago

@Larnu At least this one will be implemented one day Thanks for the suggestion. This is in our backlog but we cannot confirm when it will be implemented. :)

Thom A Over a year ago

Haha, yep, probably in SQL Server 2029 ;).

Zhorov · Accepted Answer · 2019-12-27 12:09:44Z

If you use SQL Server 2016+, you may try to split the input string using JSON built-in support. You need to transform the input text into a valid JSON array and parse this JSON array with OPENJSON() with default schema. The result is a table with columns key, value and type, and in case of JSON array the key column holds the index of the element in the specified array.

Statement:

DECLARE @json nvarchar(max) = N'06/30/2020;58044.373;.001;12/31/2020;58042.373;.003;06/30/2021;78044.373;.007'

SELECT 
   MAX(CASE WHEN CONVERT(int, [key]) % 3 = 0 THEN [value] END) AS Col1,
   MAX(CASE WHEN CONVERT(int, [key]) % 3 = 1 THEN [value] END) AS Col2,
   MAX(CASE WHEN CONVERT(int, [key]) % 3 = 2 THEN [value] END) AS Col3
FROM OPENJSON(CONCAT(N'["', REPLACE(@json, N';', N'","'), N'"]'))
GROUP BY (CONVERT(int, [key]) / 3)

Result:

----------------------------
Col1        Col2        Col3
----------------------------
06/30/2020  58044.373   .001
12/31/2020  58042.373   .003
06/30/2021  78044.373   .007

Collectives™ on Stack Overflow

String Split String With multiple columns same separator

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related