I'm trying to return the sub directory of a path parse, basically a list of user folders beneath \documents and settings\user, but \documents and settings\ could exist anywhere in the path.
Using SQL Server 2008.
So the steps are:
1) search for \documents and settings\
2) return everything before the next slash, aka \documents and settings\merv11\
Right now I'm doing something like this:
SELECT SUBSTRING(filepath, 1, CHARINDEX('\documents and settings\', filepath)) FROM dbo.directory
but it doesn't work out how I thought it would.