CODEWITHSUNDEEP
sqlsql-server
Siddharth
Siddharth

Reputation: 21

Extract a part of string between multiple delimiters

I have a column with value as '/1064_MyHoldings/ONLINE/Adhoc/Rpt_CompanyCodeElig'

Now, my requirement is to extract every value which is there between the delimeters; '1064 MyHoldings', 'ONLINE', 'Adhoc' etc?

I tried the below code, but it is only taking '1064 MyHoldings'. But I need the other values as well Can someone please help me here?

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;

Upvotes: 0

Views: 160

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

You can go for recursive search using CTE and split the strings.

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            |
+-----------------+

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use string_split():

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

Upvotes: 1

Related Questions