这是仅使用字符串函数的解决方案：

select 
    left(
        category,charindex('-',category) - 2
    ) location,substring(
        category,category) + 2,len(category) - charindex('-',category,category) + 1)
    ) category,case when charindex('-',category) + 1) > 0 
        then right(category,reverse(category)) - 2) 
    end sub_category
from t

Demo on DB Fiddle ：

location | category         | sub_category   
:------- | :--------------- | :--------------
Zoo      | Animal           | Lions          
Zoo      | Personnel        | null           
Zoo      | Operating Costs  | Power / Cooling

您也可以使用字符串分隔符。这是与您的版本兼容的出色版本。 DelimitedSplit8K

现在我们需要一些示例数据。

declare @Something table
(
    Category varchar(100)
)

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

现在我们有了一个函数并对数据进行了采样，其代码非常简洁。

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

这将返回：

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

您已将其标记为[sql-server-2017]。这意味着，您可以使用JSON支持（这是v2016引入的）。

当前，JSON是用于位置和类型安全的字符串拆分的最佳内置方法：

一个模型，模拟您的问题

DECLARE @mockup TABLE (ID INT IDENTITY,Category VARCHAR(MAX))

INSERT INTO @mockup (Category)
VALUES ('Zoo - Animals - Lions'),('Zoo - Operating Costs - Power / Cooling');

-查询

SELECT t.ID,A.[Location],A.Category,A.subCategory 
FROM @mockup t
CROSS APPLY OPENJSON(CONCAT('[["',REPLACE(t.Category,'-','","'),'"]]')) 
WITH ([Location] VARCHAR(MAX) '$[0]',Category VARCHAR(MAX) '$[1]',SubCategory VARCHAR(MAX) '$[2]') A;

结果（可能需要进行TRIM()操作）

ID  Location    Category            subCategory
1   Zoo         Animals             Lions
2   Zoo         Personnel           NULL
3   Zoo         Operating Costs     Power / Cooling

简而言之：

我们使用一些简单的字符串操作将您的字符串转换为JSON数组：

a b c    => [["a","b","c"]]

现在，我们可以将OPENJSON()与WITH子句一起使用，以固定位置返回每个片段的位置。

有点黑，但是可以用

DECLARE @t TABLE (Category VARCHAR(255))

INSERT @t (Category)
VALUES ('Zoo - Animals - Lions'),('Zoo - Operating Costs - Power / Cooling')

;WITH split_vals AS (
    SELECT Category AS Cat,TRIM(Value) AS Value,ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Category) AS RowNum
    FROM @t
    CROSS APPLY STRING_SPLIT(Category,'-')
),cols AS (
    SELECT
        Cat,CASE WHEN RowNum = 1 THEN Value END AS Location,CASE WHEN RowNum = 2 THEN Value END AS Category,CASE WHEN RowNum = 3 THEN Value END AS [Sub-Category]
    FROM split_vals
)
SELECT STRING_AGG(Location,'') AS Location,STRING_AGG(Category,'') AS Category,STRING_AGG([Sub-Category],'') AS [Sub-Category]
FROM cols
GROUP BY Cat;