This content has been marked as final. Show 2 replies
Here's one way:
The 6th argument to REGEXP_SUBSTR is like a backreference, except that it's a number, not a string.
SELECT repeat_interval , regexp_substr ( repeat_interval , '''([^'']+)''' , 1 , 1 , NULL , 1 ) as MYSTRING FROM dba_scheduler_jobs WHERE SCHEDULE_TYPE='PLSQL' AND repeat_interval LIKE 'WEEKLY%';
The 3rd, 4th and 5th arguments are the default values. I only passed them because I couldn't pass the 6th argument without all 5 earlier arguments.
The 6th argument was new in Oracle 11.1. In Oracle 10, you could use REGEXP_REPLACE instead of REGEXP_SUBSTR. Another approach people used in Oracle 10 was nesting REGEXP_SUBSTR inside TRIM, to get rid of the single-quotes REGEXP_SUBSTR returned.
Edited by: Frank Kulash on Dec 4, 2012 5:26 PM
Added reminisciences of the old days.