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
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
you will need a delimited splitter. Like DelimitedSplit8K from http://www.sqlservercentral.com/articles/Tally+Table/72993/
; with tbl as
(
select col = 'US' union all
select col = 'US-AL' union all
select col = 'US-AL-Talladega' union all
select col = 'US-AL-Talladega-35160'
)
select t.col,
max(case when ItemNumber = 1 then Item end) as Country,
max(case when ItemNumber = 2 then Item end) as State,
max(case when ItemNumber = 3 then Item end) as County,
max(case when ItemNumber = 4 then Item end) as Zip
from tbl t
cross apply dbo.[DelimitedSplit8K](t.col, '-')
group by t.col
