CODEWITHSUNDEEP
sqlsql-servert-sql
Kevin Haunstetter
Kevin Haunstetter

Reputation: 49

String Split String With multiple columns same separator

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?

Upvotes: 0

Views: 113

Answers (2)

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

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

Upvotes: 2

Related Questions