将列中的文本标准化为表格的最有效方法是什么？

如果您具有最大的列数，则在CROSS APPLY中使用一些XML。

如果未知，则必须进行动态处理。

例子

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    Pos91   Key1    Value1  Value2  Value3  Value4  Value5  NULL    NULL    NULL2   Key2    Value1  Value2  Value3  Value4  Value5  NULL    NULL    NULL3   Key3    Value1  Value2  Value3  Value4  Value5  NULL    NULL    NULL

编辑

我应该添加，这

ltrim(rtrim(...))

varchar(max)

编辑-以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