SQL Language (MOSC)

MOSC Banner

substr ( instr ) functions

edited Apr 3, 2012 5:22PM in SQL Language (MOSC) 3 commentsAnswered
How do I select archive logs only from the C: drive not the drive?
 select SUBSTR(NAME, instr(NAME, 'C', 1) -1) FROM v$archived_log
/


C:\ORADATA\ORCL1\ARCHIVELOG\ORCL1_1_117_769353184.ARC
RCL1\ARCHIVELOG\ORCL1_1_118_769353184.ARC
RCL1\ARCHIVELOG\ORCL1_1_119_769353184.ARC
C:\ORADATA\ORCL1\ARCHIVELOG\ORCL1_1_119_769353184.ARC
RCL1\ARCHIVELOG\ORCL1_1_120_769353184.ARC
C:\ORADATA\ORCL1\ARCHIVELOG\ORCL1_1_120_769353184.ARC

I have the above sql that select archives from the C: drive correctly but then picks up the archives from the drive by going up to the first 'C' in ORCL.

I only want archives from the C: drive listed out.

How would I change the sql to do this?  This is 9i so no regexps.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center