Split string with recursive cte


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

Enter Parameters

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