Split string with recursive cte

0

Please login or register to vote for this query.

(click on this box to dismiss)

Finding positions using charindex

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

-- Split string with recursive cte
-- Finding positions using charindex

declare @T table 
(
  ID int identity,
  emp_name varchar(25),
  [path] varchar(150)
)

insert into @T values
('Spare', 'DMS Office/Spare'),
('Administration',   'DMS Office/Administration'),
('Administration',  'XCN Ulan/Administration'),
('Shed 3',    'XCN Ulan/Shed 3'),
('Administration',  'Michael Scott Paper Company/Administration')

declare @EmpName varchar(25) = 'Tom'

;with cte(Sort, P1, P2, [path]) as
(
  select 1,
         1,
         charindex('/', [path]+'/', 1),
         [path]
  from @T
  where emp_name = @EmpName  
  union all
  select Sort+1,
         P2+1,
         charindex('/', [path]+'/', C.P2+1),
         [path]
  from cte as C
  where charindex('/', [path]+'/', C.P2+1) > 0
)
select substring([path], P1, P2-P1)
from cte
order by Sort

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results