SQL-通过多个定界符将字符串拆分为列 [英] SQL - Split string to columns by multiple delimiters

问题描述

似乎有很多解决方案，但是我的解决方案必须动态，因为分隔符的数量在0到3之间变化，并且必须相对有效，因为它将在5个循环中跨> 10m行运行。

There appear to be numerous solutions to this problem, however my solutions needs to be dynamic as the number of delimiters changes from between 0 and 3 and needs to be relatively efficient as it will be running across >10m rows across 5 loops.

例如：

  US

  US-AL

  US-AL-Talladega

  US-AL-Talladega-35160

如果信息不在字符串中，则解决方案将需要能够将每个项目存放在具有NULL字段的Country，State，County，ZIP字段中。

The solution would need to be able to deposit each item in a Country, State, County, ZIP field with a NULL field if the information is not within the string.

任何对最佳方法的评论将不胜感激，甚至指出我可能会错过解决方案的方向

Any comments on the best approach would be appreciated or even point me in the direction of where I may have possible missed a solution would be much appreciated

推荐答案

另一种选择是将XML与CROSS或OUTER APPLY配合使用

Another option is with a little XML in concert with a CROSS or OUTER APPLY

示例

Declare @YourTable table (YourCol varchar(100))
Insert Into @YourTable values
 ('US')
,('US-AL')
,('US-AL-Talladega')
,('US-AL-Talladega-35160')

Select A.* 
      ,B.*
 From @YourTable A
 Outer Apply (
                Select Country = xDim.value('/x[1]','varchar(max)')
                      ,State   = xDim.value('/x[2]','varchar(max)')
                      ,County  = xDim.value('/x[3]','varchar(max)')
                      ,ZIP     = xDim.value('/x[4]','varchar(max)')
                From  (Select Cast('<x>' + replace(YourCol,'-','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

返回

YourCol                 Country State   County      ZIP
US                      US      NULL    NULL        NULL
US-AL                   US      AL      NULL        NULL
US-AL-Talladega         US      AL      Talladega   NULL
US-AL-Talladega-35160   US      AL      Talladega   35160

这篇关于SQL-通过多个定界符将字符串拆分为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！