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
Friday, February 17, 2012
extracting data
Labels:
admin,
capital,
database,
extracting,
facilities,
field,
following,
full_path,
improvements,
informaton,
management,
microsoft,
mysql,
newutil,
oracle,
platte,
program,
server,
south,
sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment