Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Use of INSTR to strip directory and extension from file

Use of INSTR to strip directory and extension from file

From: Paul <paulwragg2323_at_hotmail.com>
Date: 15 Feb 2006 07:28:13 -0800
Message-ID: <1140017293.681778.188960@g14g2000cwa.googlegroups.com>


Hi

I needed a way of stripping a directory and a file extension from a column in the database.

i.e. extracting 'File1'
from
'c:\Program Files\A Directory\File1.file'

I came up with the following:

SELECT SUBSTR(COLUMN1, INSTR(COLUMN1, '\', -1) + 1, DECODE(INSTR(SUBSTR(COLUMN1, INSTR(COLUMN1, '\', -1) + 2), '.', 1 ), 0, 250, INSTR(SUBSTR(COLUMN1, INSTR(COLUMN1, '\', -1) + 2), '.', 1 ))) FROM TABLE_1; Note that this needs to work if there is no directory or extension hence the decode. Can anybody improve on this or offer a more readable alternative?

Thanks,

Paul Received on Wed Feb 15 2006 - 09:28:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US