sql - 将列中的文本规范化为表的最有效方法是什么？

Question

在 T-SQL 中，我有一个包含一些文本的列，格式如下:

[Key1:Value1:Value2:Value3:Value4:Value5]
[Key2:Value1:Value2:Value3:Value4:Value5]
[Key3:Value1:Value2:Value3:Value4:Value5]

其中可以有任意数量的括号集，但通常在 3 到 6 之间。我正在寻找一种方法来快速将它们格式化为临时表或表变量，以便我可以报告数据。例如，我希望表格格式为:

|Key|Column 1|Column 2|Column 3|Column 4|Column 5|  
|Key 1|Value 1|Value 2|Value 3|Value 4|Value 5|  
|Key 2|Value 1|Value 2|Value 3|Value 4|Value 5|  
|Key 3|Value 1|Value 2|Value 3|Value 4|Value 5|  

我知道这正在插入 SQL 的极限，应该通过修改应用程序来处理，但我希望现在我可以用 T-SQL 做一些聪明的事情。

Answer 1

如果您有最大列数，则在 CROSS APPLY 中添加一些 XML。

如果未知，则必须使用 DYNAMIC。

示例

Declare @YourTable Table ([ID] varchar(50),[SomeCol] varchar(50))
Insert Into @YourTable Values 
 (1,'[Key1:Value1:Value2:Value3:Value4:Value5]')
,(2,'[Key2:Value1:Value2:Value3:Value4:Value5]')
,(3,'[Key3:Value1:Value2:Value3:Value4:Value5]')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                      ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                From  (Select Cast('<x>' + replace(replace(replace(SomeCol,'[',''),']',''),':','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

返回

ID  Pos1    Pos2    Pos3    Pos4    Pos5    Pos6    Pos7    Pos8    Pos9
1   Key1    Value1  Value2  Value3  Value4  Value5  NULL    NULL    NULL
2   Key2    Value1  Value2  Value3  Value4  Value5  NULL    NULL    NULL
3   Key3    Value1  Value2  Value3  Value4  Value5  NULL    NULL    NULL

EDIT

我应该补充一点，ltrim(rtrim(...)) 是可选的，varchar(max) 是我的示范性默认值。

EDIT - One String delimited with CRLF

Declare @S varchar(max)='
[Key1:Value1:Value2:Value3:Value4:Value5]
[Key2:Value1:Value2:Value3:Value4:Value5]
[Key3:Value1:Value2:Value3:Value4:Value5]
'

Select B.*
 From  (
        Select RetSeq = Row_Number() over (Order By (Select null))
              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
        From  (Select x = Cast('<x>' + replace(@S,char(13)+char(10),'</x><x>')+'</x>' as xml).query('.')) as A 
        Cross Apply x.nodes('x') AS B(i)
       ) A
 Cross Apply (
               Select  Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                      ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                From  (Select Cast('<x>' + replace(replace(replace(RetVal,'[',''),']',''),':','</x><x>')+'</x>' as xml) as xDim) as A 
       ) B
 Where A.RetVal is not null