Parsing Text Field

[LAM986]

Hello,
I am querying the Catalog table in the ReportServer for column [Path] which houses the location of a report. I want to parse that to return only the main folder. So the data reads /CSS/Management. I only want CSS. Can anyone direct me how to do that. This is what I have so far, any help would be much appreciated. Thank you.

SELECT LTrim(RTrim([Path])
, charindex(' ', [Path])
, LTrim(RTrim(substring([path], 1, charindex(' ', [path]))))
FROM [Catalog]

 

[bborissov]

If you ALWAYS have the path stored like this:
/main/sub.....
try this:

declare @TestPath as varchar(8000)
SET @TestPath = '/CSS/Management'

SELECT CASE WHEN CHARINDEX('/',@TestPath,2) = 0
                 THEN @TestPath
            ELSE LEFT(@TestPath,CHARINDEX('/',@TestPath,2)-1) END

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[LAM986]

Thank you!!!