Split string of variable length, variable delimiters

Question

Read the question posed here, but mine is a little more complicated.

I have a string that is variable in length, and the delimiter can sometimes be two dashes, or sometimes it can be just one. Let's say in my table the data that I want to break out is stored in a single column like this:

+ -----------------------------------------+
| Category                                 |
+------------------------------------------+
| Zoo - Animals - Lions                    |
| Zoo - Personnel                          |
| Zoo - Operating Costs - Power / Cooling  |
+------------------------------------------+

But I want to output the data string from that single column into three separate columns like this:

+----------+--------------------+-----------------+
| Location | Category           | Sub-Category    |
+----------+--------------------+-----------------+
| Zoo      | Animals            | Lions           |
| Zoo      | Personnel          |                 |
| Zoo      | Operating Costs    | Power / Cooling |
+----------+--------------------+-----------------+

Hoping for some guidance as the samples I've been finding on Google seem to be simpler than this.

Answer 1

You can also use a string splitter. Here is an excellent one that works with your version. DelimitedSplit8K

Now we need some sample data.

declare @Something table
(
    Category varchar(100)
)

insert @Something values
('Zoo - Animals - Lions')
, ('Zoo - Personnel')
, ('Zoo - Operating Costs - Power / Cooling')

Now that we have a function and sample data the code for this is quite nice and tidy.

select s.Category
    , Location = max(case when x.ItemNumber = 1 then Item end)
    , Category = max(case when x.ItemNumber = 2 then Item end)
    , SubCategory = max(case when x.ItemNumber = 3 then Item end)
from @Something s
cross apply dbo.DelimitedSplit8K(s.Category, '-') x
group by s.Category

And this will return:

Category                                |Location|Category       |SubCategory
Zoo - Animals - Lions                   |Zoo     |Animals        |Lions
Zoo - Operating Costs - Power / Cooling |Zoo     |Operating Costs|Power / Cooling
Zoo - Personnel                         |Zoo     |Personnel      |NULL