sql server - SQL - Split string to columns by multiple delimiters

Question

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.

As example:

  US

  US-AL

  US-AL-Talladega

  US-AL-Talladega-35160

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

Answer 1

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

Example

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

Returns

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