Using Substring and Charindex to split a text in to columns

SELECT C.Path AS OrgInput
, CHARINDEX('/', C.Path, 2) AS [FirstPos]
, SUBSTRING(C.Path -- String
, CHARINDEX('/', C.Path, 2)+1 -- StartPos
, LEN(C.Path) -- Length
) AS StartText

, LEFT(SUBSTRING(C.Path -- String
, CHARINDEX('/', C.Path, 2)+1 -- StartPos
, LEN(C.Path) -- Length
)
, 8
) AS FirstResult

, LEFT(SUBSTRING(C.Path -- String
, CHARINDEX('/', C.Path, 2)+1 -- StartPos
, LEN(C.Path) -- Length
)
, CHARINDEX('/', C.Path, (CHARINDEX('/', C.Path, 2)+1)) -- Test toegevoegd
) AS SecondResult

, CHARINDEX('/', C.Path, (CHARINDEX('/', C.Path, 2)+1)) AS [Second/]
, SUBSTRING(C.Path
, CHARINDEX('/', C.Path, (CHARINDEX('/', C.Path, 2)+1))+1
, LEN(C.Path)
)
FROM dbo.Catalog C WITH(NOLOCK) -- 773 Report Catalog
WHERE 1=1
AND C.Type = 2
ORDER BY 1

/*
SUBSTRING(Text, Start, Length)
LEFT(Text, Length) --> Keep 3 most left chars
RIGHT(Text, Length) --> Keep 3 most right chars
LEN(Text) --> Length
*/

;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), '/')

Sean Lange (12/18/2012)

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

SELECT( SELECT C.Path AS Path, C.Name AS ReportName
, CASE WHEN C.Hidden = 1 THEN 'True'
ELSE 'False' END AS ReportHide
, COALESCE(C.Description, '<none>') AS ReportDesc
, UC.UserName AS ReportCreatedBy, C.CreationDate AS ObjectCreatedDate
, UM.UserName AS ReportModifiedBy, C.ModifiedDate AS ObjectModifedDate
, D.*
FROM dbo.Catalog C
INNER JOIN dbo.Users UC WITH(NOLOCK)
ON C.CreatedByID = UC.UserID
INNER JOIN dbo.Users UM WITH(NOLOCK)
ON C.ModifiedByID = UM.UserID
CROSS APPLY Ceyenne_JDE.dbo.DelimitedSplit8K(C.Path, '/') D
WHERE 1=1
AND C.Type = 2
-- AND C.Path = '/Adhoc/CRM/20121024 CRM complaints report on owner level'
) Q
PIVOT ( MAX(Item)
FOR ItemNumber IN ([2], [3], [4], [5], [6], [7], [8])
) AS pvt
)