Split values in brackets into columns-CodePudding

I have a column containing data encapsulated in <::>. I want to split these pieces into separate columns.

Data column looks like this:

<:abc:><:xyz:><:dds:>
<:dae:><:xr:><:s:>

Desired result

Col1  Col2  Col3
----------------
abc   xyz   dds
dae   xr    s

CodePudding user response：

Be carefull with STRING_SPLIT(). It does not guarantee to return the expected sort order.

My suggestion uses XML. Try this:

--Some test data in a declared table variable

DECLARE @tbl TABLE(ID INT IDENTITY, YourString VARCHAR(1000));
INSERT INTo @tbl(YourString) VALUES('<:abc:><:xyz:><:dds:>'),
                                   ('<:dae:><:xr:><:s:>');

--the query

SELECT A.x.value('x[1]','varchar(1000)') AS col1
      ,A.x.value('x[2]','varchar(1000)') AS col2
      ,A.x.value('x[3]','varchar(1000)') AS col3
FROM @tbl t
CROSS APPLY(SELECT CAST(REPLACE(REPLACE(t.YourString,'<:','<x>'),':>','</x>') AS XML)) A(x);

The idea in short

We replace your separators in order to get a castable XML.
We use an APPLY to add this casted XML to our result set.
We fetch each element by its position (XQuery)

Starting with v2016 the recommended approach uses JSON for this:

SELECT JSON_VALUE(A.j,'$[0]') AS col1
      ,JSON_VALUE(A.j,'$[1]') AS col2
      ,JSON_VALUE(A.j,'$[2]') AS col3
FROM @tbl t
CROSS APPLY(SELECT REPLACE(REPLACE(REPLACE(t.YourString,':><:','","'),'<:','["'),':>','"]')) A(j);

... or this (see the doubled square brackets):

SELECT A.*
FROM @tbl t
CROSS APPLY OPENJSON(REPLACE(REPLACE(REPLACE(t.YourString,':><:','","'),'<:','[["'),':>','"]]'))
            WITH(col1 VARCHAR(1000) '$[0]'
                ,col2 VARCHAR(1000) '$[1]'
                ,col3 VARCHAR(1000) '$[2]') A;

The idea is the same...

Hint: Within XML (elements) and JSON-arrays the sort order is secure.

CodePudding user response：

You could use string_split for this however it's not guaranteed to always return rows in the same order so I prefer to use OpenJson for small data sets as it provides a consistent index.

with d as (
    select '['   Stuff(Translate(data,':<>','", '),1,1,'')   ']' data
    from t
)
select 
    max(case when j.[key]=0 then j.[value] end) Col1,
    max(case when j.[key]=1 then j.[value] end) Col2,
    max(case when j.[key]=2 then j.[value] end) Col3
from d
cross apply OpenJson(d.data)j
group by d.data

Demo Fiddle

CodePudding user response：

with CTE as (
SELECT A.x.value('x[1]','varchar(500)') AS P01
      ,A.x.value('x[2]','varchar(500)') AS P02
      ,A.x.value('x[3]','varchar(500)') AS P03
      ,A.x.value('x[4]','varchar(500)') AS P04
      ,A.x.value('x[5]','varchar(500)') AS P05
      ,t.*
FROM [LIST] t

CROSS APPLY(SELECT CAST(REPLACE(REPLACE(t.[JOB_PARAMETER],'<:','<x>'),':>','</x>') AS XML)) A(x)
  
 )
SELECT [ID]
      ,[TYP]
      ,[PARAMETER]
     ,P01
     ,P02
     ,P03
     ,P04
     ,P05


FROM CTE