Path enumeration model, how to get superiors?

Consider the table data below:

emp_name VARCHAR(25开发者_如何学Go), path VARCHAR(150)
Albert                /Albert
John                  /Albert/John
Chuck                 /Albert/Chuck
Tom                   /Albert/John/Tom
Frank                 /Frank

I want to get a list of superiors of Tom:

John
Albert

(can include Tom)

Is it possible to do with without splitting the path and then using a sequence table (only way I found)?

DB is Sql server 2008 R2

You can use a recursive CTE to split the hierarchy string value.

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

insert into @T values
('Albert', 'Albert'),
('John',   'Albert/John'),
('Chuck',  'Albert/Chuck'),
('Tom',    'Albert/John/Tom'),
('Frank',  'Frank')

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

Result:

(No column name)
Albert
John
Tom

Test the query here: https://data.stackexchange.com/stackoverflow/q/101383/

Another thing you can try

select T2.emp_name
from @T as T1
  inner join @T as T2
    on '/'+T1.[path]+'/' like '%/'+T2.emp_name+'/%' and
       T2.emp_name <> @EmpName  
where T1.emp_name = @EmpName

https://data.stackexchange.com/stackoverflow/q/101518/get-hierarchy-with-join-using-like