Here is an example using the DelimitedSplit8K function that you can find at the link provided, or you can follow the link in my signature for splitting strings.

;with cte (Col1) as

(

select '/Adhoc/CRM/Complaints report/Report on owner level' union all

select '/Adhoc/Finance/General Ledger/Spend Analysis report'

)

select *

from cte

cross apply dbo.DelimitedSplit8K(right(Col1, len(Col1) -1), '/')

Notice how I posted data in an easily consumable format. You should consider that on future posts.

Also, I noticed you are using the NOLOCK hint. Are you aware of the challenges that hint brings to the table? It will help your code run faster but it comes at a cost. Most people say "I don't care about dirty reads". This hint has far greater implications than just dirty reads. You can missing or even duplicate information. Here are a couple of articles that explains this hint in greater detail.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx