Re: Substr and remove characters from field

From: Anton Buijs <remove_aammbuijs_at_xs4all.nl>
Date: Wed, 23 Jul 2003 21:49:28 +0200
Message-ID: <3f1ee6aa$0$49099$e4fe514c@news.xs4all.nl>

jakemoore <member34054_at_dbforums.com> schreef in berichtnieuws 3144518.1058988298_at_dbforums.com...
|
| What I would like to be able to do is have a unc path with file name
| like: c:\test\hello\people\file.tif and have a substr command in Oracle
| remove everything but the filename minus extension. This would have to
| be able to adapt for different paths and file names.
|
| I have a list of a few hundred file paths in an oracle table I would
| like to perform this on.
|
|
| thanks
|
| --
| Posted via http://dbforums.com

SQL> def fullpath=C:\x\y\z\file.ext
SQL> select substr('&&fullpath',instr('&&fullpath','\',-1)+1) from dual; old 1: select substr('&&fullpath',instr('&&fullpath','\',-1)+1) from dual new 1: select
substr('C:\x\y\z\file.ext',instr('C:\x\y\z\file.ext','\',-1)+1) from dual

SUBSTR('

---

file.ext

Is this what you want? Or do you want the extention stripped off too? I think you can figure this out now yourself.

The trick is that if the position argument of the INSTR function is negative, it starts counting from the right, where a positive value starts from the left. Easy..... when you know it. You can find it in the description of the INSTR function in the SQL Reference. Same goes for SUBSTR too. Received on Wed Jul 23 2003 - 14:49:28 CDT