在多个分隔符之间提取字符串的一部分

Question

我有一列的值为'/1064_MyHoldings/ONLINE/Adhhoc/Rpt_CompanyCodeElig'

现在，我的要求是提取熟食店之间的每一个值1064 MyHoldings、ONLINE、Adhoc等？

我尝试了下面的代码，但它只需要"1064 MyHoldings"。但我也需要其他价值观有人能帮我吗？

WITH yourTable AS (
SELECT '/1064_MyHoldings/ONLINE/Adhoc/Rpt_CompanyCodeElig' AS Path
)
SELECT
CASE WHEN Path LIKE '%/%/%' THEN
SUBSTRING(Path,
CHARINDEX('/', Path) + 1,
CHARINDEX('/', Path, CHARINDEX('/', Path) + 1) - CHARINDEX('/', Path) - 1)
ELSE 'NA' END AS first_component
FROM yourTable;

Answer 1

使用string_split():

select s.value
from t cross apply
string_split(path, '/') s

Answer 2

您可以使用CTE进行递归搜索并拆分字符串。

WITH yourTable AS (
SELECT '/1064_MyHoldings/ONLINE/Adhoc/Rpt_CompanyCodeElig' AS Path
),
cte_splitTable as
(
SELECT value as val, 1 as lvl
from yourTable
cross apply 
string_split(Path,'_')
UNION ALL
SELECT t.value as val, lvl+1 as lvl
from cte_splitTable as c
cross apply
string_split(c.val,'/') as t
where CHARINDEX('/',val) > 0
)
select *  from cte_splitTable
where PATINDEX('%[_/]%',val) = 0 and len(val) > 0

+-----------------+
|       val       |
+-----------------+
| CompanyCodeElig |
| MyHoldings      |
| ONLINE          |
| Adhoc           |
| Rpt             |
| 1064            |
+-----------------+