将括号中的值分割成列

Question

我有一个包含封装在<::>中的数据的列。我想把这些部分分成单独的列。

数据列如下:

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

想要的结果

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

Answer 1

小心STRING_SPLIT()。它不保证返回预期的排序顺序。

我建议使用XML。试试这个:

——声明的表变量

中的一些测试数据

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

——查询

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);

简而言之

• 我们替换你的分隔符是为了得到一个可浇注的XML。
• 我们使用APPLY将此转换XML添加到我们的结果集中。
• 根据每个元素的位置(XQuery)获取

从v2016开始，推荐使用JSON:

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);

…或者这样(见双方括号):

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;

道理是一样的…

提示:在XML(元素)和json数组中，排序顺序是安全的。

Answer 2

您可以使用string_split进行此操作，但不能保证始终以相同的顺序返回行，因此我更喜欢使用OpenJson用于小型数据集，因为它提供了一致的索引。

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

演示小提琴

Answer 3

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