substr ( instr ) functions
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.
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.
0