December 28, 2010 at 12:44 pm
Right now i have a field like this, which is a combination of path and file name: /folder/folder1/folder2/12345-ABCD.txt
I want to select LEVEL 3 section like this: /folder2/
Thanks for any help this.
December 28, 2010 at 12:47 pm
Use the CHARINDEX function
eg from the BOL.
DECLARE @document varchar(64)
SELECT @document = 'Reflectors are vital safety' +
' components of your bicycle.'
SELECT CHARINDEX('bicycle', @document)
GO
December 28, 2010 at 12:55 pm
Besides finding the Position of the folder. i would like to display the name of the folder from the LEVEL3
the output should be: /folder2/
December 28, 2010 at 1:24 pm
you rock Jim.
I just found other way.
Select
reverse(substring(reverse(PATH),charindex('/',reverse(PATH)), charindex('/',reverse(PATH),charindex('/',reverse(PATH))+1) - charindex('/',reverse(PATH))+1)) as foldername
FROM table
December 28, 2010 at 1:30 pm
You could also use the split function and get a list of all the folders:
SELECT * FROM dbo.fn_Split('/folder/folder1/folder2/12345-ABCD.txt','/')
WHERE IDX = 3
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 28, 2010 at 8:35 pm
Sahasam (12/28/2010)
you rock Jim.I just found other way.
Select
reverse(substring(reverse(PATH),charindex('/',reverse(PATH)), charindex('/',reverse(PATH),charindex('/',reverse(PATH))+1) - charindex('/',reverse(PATH))+1)) as foldername
FROM table
That's going to be a performance problem. REVERSE is one of the more "expensive" functions to use and you use it 6 times on each row. I'd chose one of the other methods.
--Jeff Moden
December 28, 2010 at 8:37 pm
toddasd (12/28/2010)
You could also use the split function and get a list of all the folders:SELECT * FROM dbo.fn_Split('/folder/folder1/folder2/12345-ABCD.txt','/')
WHERE IDX = 3
I notice you're using "fn_Split"... is that the splitter from MSDN that also has the WHILE loop in it?
--Jeff Moden
December 28, 2010 at 8:53 pm
Actually no. I had to go back in my files and look, but it's the one that uses the tally table.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply