extracting data

I have the following informaton in a field called: full_path
/Capital Improvements Program/Management/Facilities/Lower South Platte/New
Util Admin Bldg/Ops Ctr/Planning
How can I pull out the information independently from the 2nd and 3rd level
or the 2nd and 3rd level combined. re: /Management
or /Facilities or combined /Management/facilities ?For such problems, if you have to do this at the server, use a table of
Numbers. That will make the solution more generic and flexible.
CREATE TABLE Nbrs ( n INT NOT NULL PRIMARY KEY );
And populate the table with numbers from 1 to 8000. Search the archives of
this newsgroup for several shortcuts to generate such sequential numbers.
And the query assuming the string always start with a '/':
DECLARE @.s VARCHAR(200), @.level INT
SET @.s = '/Capital Improvements Program/Management/Facilities/Lower South
Platte/New Util Admin Bldg/Ops Ctr/Planning'
SET @.level = 3
SELECT SUBSTRING( @.s, n, CHARINDEX('/', @.s + '/', n ) - n )
FROM Nbrs
WHERE n BETWEEN 2 AND LEN( @.s ) + 1
AND SUBSTRING('/' + @.s, n, 1) = '/'
AND n - LEN(REPLACE( LEFT( @.s, n ), '/', '' ) ) = @.level ;
For reuse and ease of maintanence, you can make it a procedure or scalar
UDF, depending on your requirements.
Anith