Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Use of INSTR to strip directory and extension from file
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