SQL cut off characters 3

[pseud0phreak]

Hi all,
i am trying to extract the folder path from the files "path" column in my table. using sql on oracle 10g
so the format of the path is \\network\folder1\folder2\folder3\filename.txt
so bascially i want just the \\network\foldera\folderb\folderc
part of it.
so cut off everything after the last "\"
pretty much every folder name is different so no other pattern matching available.

i have something very close to what i need in excel
=LEFT(A2, SEARCH("\",A2)) but this only finds the first "\" which gets me no where since the first character is a \.

any ideas?
Thanks

 

[Beilstwh]

substr(a2,1,instr(a2,'/',-1)-1)

Bill
Lead Application Developer
New York State, USA

 

[SantaMufasa]

Pseudo,

Here is one solution (of many):

select * from pseudo;

STR
----------------------------------------------
\\network\folder1\folder2\folder3\filename.txt

select substr(str,1,instr(str,'\',-1)-1) result from pseudo;

RESULT
---------------------------------
\\network\folder1\folder2\folder3

If you have questions about the code, please re-post.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Beilstwh]

Darn, i used a forward slash in my code, not a back slash...lol been on unix too much.

Bill
Lead Application Developer
New York State, USA

 

[SantaMufasa]

...and during the two minutes that it took me to compose code, I cross-posted pretty much the same thing with Bill. (Too bad that Tek-Tips cannot tell you that someone has posted to the same thread to which you are responding. )

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Beilstwh]

have a star for being the one to get it RIGHT first.

Bill
Lead Application Developer
New York State, USA

 

[pseud0phreak]

thanks guys, exactly what i needed, Bill, saw your post and noticed the wrong slash, easy fix
stars for both