2 Replies Latest reply: Dec 4, 2012 11:00 PM by jeneesh RSS

    regexp_substr question

    EdStevens
      Oracle 11.2.0.1 SE-One
      Oracle Linux 5.6 x86-64

      Given
      SELECT
        repeat_interval
      , regexp_substr(repeat_interval,'''[^'']+''') MYSTRING
      FROM
        dba_scheduler_jobs
      WHERE
        SCHEDULE_TYPE='PLSQL' AND repeat_interval LIKE 'WEEKLY%';
      returns
      WEEKLY(.417,'THURSDAY')     'THURSDAY'
      I need to strip the single quotes from the result, giving
      WEEKLY(.417,'THURSDAY')     THURSDAY
      Still struggling with regexp, especially when the delimiting single quote is part of the string to be evaluated.
        • 1. Re: regexp_substr question
          Frank Kulash
          Hi,

          Here's one way:
          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 6th argument to REGEXP_SUBSTR is like a backreference, except that it's a number, not a string.
          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.
          • 2. Re: regexp_substr question
            jeneesh
            Or replace
            regexp_replace(repeat_interval,'.*?''([^'']+)''.*$','\1')
            Edited by: jeneesh on Dec 5, 2012 10:29 AM