This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Dec 6, 2012 4:13 AM by chris227 RSS

REGEXP_REPLACE - Is this strange Or Am I Missing something?

jeneesh Guru
Currently Being Moderated
Hi All,

I was trying to use REGEXP_REPLACE to solve regexp_substr question

And feels strange, when seeing the difference between the below two outputs STRING1 and STRING2 ( My DB - Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production).

The only difference in the two patterns is the additional "$" at the end in the first pattern
with t as
(select 'WEEKLY(.417,''THURSDAY'')x' repeat_interval from dual)
SELECT
  repeat_interval,
  regexp_replace(repeat_interval,'.*?''([^'']+)''.*$','\1') STRING1,
  regexp_replace(repeat_interval,'.*?''([^'']+)''.*','\1') STRING2
FROM  t;

REPEAT_INTERVAL          STRING1         STRING2       
------------------------ --------------- ---------------
WEEKLY(.417,'THURSDAY')x THURSDAY        THURSDAY)x  
Is it strange ( Or I need a coffee?)

Thanks,
jeneesh
  • 1. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    ranit B Expert
    Currently Being Moderated
    And changing + to *
    with t as
    (select 'WEEKLY(.417,''THURSDAY'')x' repeat_interval from dual)
    SELECT
      repeat_interval,
      regexp_replace(repeat_interval,'.*?''([^'']+)''.*$','\1') STRING1,
      regexp_replace(repeat_interval,'.*?''([^'']+)''.+$','\1') STRING2, /* '*$' changed to '+$' */
      regexp_replace(repeat_interval,'.*?''([^'']+)''.*','\1') STRING3, /* added new */
      regexp_replace(repeat_interval,'.*?''([^'']+)''.+','\1') STRING4 /* added new*/
    FROM  t;
    gives
    WEEKLY(.417,'THURSDAY')x     THURSDAY     THURSDAY)x     THURSDAY     THURSDAYx
    1 quick qn... Do we use $ in Regexp for Oracle? In PERL we use it, but not in Oracle i guess



    Edited by: ranit B on Dec 5, 2012 11:05 AM
    -- added REGEXP 3 & 4
  • 2. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    jeneesh Guru
    Currently Being Moderated
    ranit B wrote:
    1 quick qn... Do we use $ in Regexp for Oracle? In PERL we use it, but not in Oracle i guess
    RTFM<a/>
  • 3. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    ranit B Expert
    Currently Being Moderated
    Try putting the '.' inside round braces '()'
    with t as
    (select 'WEEKLY(.417,''THURSDAY'')x' repeat_interval from dual)
    SELECT
      repeat_interval,
      regexp_replace(repeat_interval,'.*?''([^'']+)''.*$','\1') STRING1,
      regexp_replace(repeat_interval,'.*?''([^'']+)''.*','\1') STRING2,
      regexp_replace(repeat_interval,'.*?''([^'']+)''(.)*','\1') STRING3,
      regexp_replace(repeat_interval,'.*?''([^'']+)''(.)*$','\1') STRING4
    FROM  t;
    gives
    WEEKLY(.417,'THURSDAY')x     THURSDAY     THURSDAY)x     THURSDAY     THURSDAY
  • 4. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    jeneesh Guru
    Currently Being Moderated
    Friend,

    Thanks for the response.

    I am not trying to solve an issue..In fact it is already solved by putting $.

    Just trying to understand the difference between the two patterns given in my first post.. :)

    Thanks.
  • 5. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    ranit B Expert
    Currently Being Moderated
    I'm not sure and very new to give you suggestions...
    But might be there is a syntactical rule that... before '$' any pattern should be present in '()' like '(.)*' or '{.}' and simple '.*' will not work.

    Ranit B.
  • 6. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    jeneesh Guru
    Currently Being Moderated
    Just needed a coffee...Clear now..
  • 7. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    Manik Expert
    Currently Being Moderated
    Looks like Frank always has coffee mug beside him, his answer is really cool (as always) in that thread. ;)

    Cheers,
    Manik.
  • 8. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    chris227 Guru
    Currently Being Moderated
    jeneesh wrote:
    Just needed a coffee...Clear now..
    Not complete to me yet ...
    with t as
    (select 'WEEKLY(.417,''THURSDAY'')x' repeat_interval from dual)
    SELECT
      repeat_interval,
      '|'||regexp_replace(repeat_interval,'.*?''([^'']+)''.*$','\1')||'|' STRING1,
      '|'||regexp_replace(repeat_interval,'.*?''([^'']+)''.*','\1')||'|' STRING2,
      '|'||regexp_replace(repeat_interval,'.*''([^'']+)''.*','\1')||'|' STRING3
    FROM  t;
    
    REPEAT_INTERVAL     STRING1     STRING2     STRING3
    WEEKLY(.417,'THURSDAY')x     |THURSDAY|     |THURSDAY)x|     |THURSDAY|
    
    BANNER
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
    PL/SQL Release 10.2.0.5.0 - Production
    CORE     10.2.0.5.0     Production
    TNS for Solaris: Version 10.2.0.5.0 - Production
    NLSRTL Version 10.2.0.5.0 - Production
  • 9. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    Sven W. Guru
    Currently Being Moderated
    chris227 wrote:
    jeneesh wrote:
    Just needed a coffee...Clear now..
    Not complete to me yet ...
    Nor for me.
    It looks like a bug to me. " .* " should always be greedy unless followed by a " ? " which would make it non-greedy.
    But it seems as if the second " .* " will keep the greedyness of the first " .* ".

    @Manik: There was an answer from Frank? Can't see it in this thread.
  • 10. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    chris227 Guru
    Currently Being Moderated
    Sven W. wrote:
    It looks like a bug to me. " .* " should always be greedy unless followed by a " ? " which would make it non-greedy.
    But it seems as if the second " .* " will keep the greedyness of the first " .* ".
    That's what i thought on this.
    @Manik: There was an answer from Frank? Can't see it in this thread.
    This was related to the thread metioned by jeneesh: regexp_substr question
  • 11. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    AlbertoFaenza Expert
    Currently Being Moderated
    jeneesh wrote:
    Hi All,

    I was trying to use REGEXP_REPLACE to solve regexp_substr question

    And feels strange, when seeing the difference between the below two outputs STRING1 and STRING2 ( My DB - Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production).

    The only difference in the two patterns is the additional "$" at the end in the first pattern
    with t as
    (select 'WEEKLY(.417,''THURSDAY'')x' repeat_interval from dual)
    SELECT
    repeat_interval,
    regexp_replace(repeat_interval,'.*?''([^'']+)''.*$','\1') STRING1,
    regexp_replace(repeat_interval,'.*?''([^'']+)''.*','\1') STRING2
    FROM  t;
    
    REPEAT_INTERVAL          STRING1         STRING2       
    ------------------------ --------------- ---------------
    WEEKLY(.417,'THURSDAY')x THURSDAY        THURSDAY)x  
    Is it strange ( Or I need a coffee?)

    Thanks,
    jeneesh
    Hi Jeneesh,

    enclosing between brackets the .* seems to work correctly:
    with t as
    (select 'WEEKLY(.417,''THURSDAY'')x' repeat_interval from dual)
    SELECT
      repeat_interval,
      regexp_replace(repeat_interval,'.*?''([^'']+)''.*$','\1') STRING1,
      regexp_replace(repeat_interval,'.*?''([^'']+)''.*','\1') STRING2,
      regexp_replace(repeat_interval,'(.*)?''([^'']+)''.*$','\2') STRING3,
      regexp_replace(repeat_interval,'(.*)?''([^'']+)''.*','\2') STRING4
    FROM  t;
    
    REPEAT_INTERVAL          STRING1         STRING2         STRING3         STRING4        
    ------------------------ --------------- --------------- --------------- ---------------
    WEEKLY(.417,'THURSDAY')x THURSDAY        THURSDAY)x      THURSDAY        THURSDAY       
    As Sven suggested this might be a bug.

    Regards.
    Al
  • 12. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    Sven W. Guru
    Currently Being Moderated
    Another unexpected behaviour:

    can anyone explain why we see 3 minus chars in string4?
    with t as
    (select q'[WEEKLY(.417,'THURSDAY')x]' repeat_interval from dual)
    SELECT
      repeat_interval,
      '|'||regexp_replace(repeat_interval,'.*?''([^'']+)''.*$','\1')||'|' STRING1,
      '|'||regexp_replace(repeat_interval,'.*?''([^'']+)''.*','-\1-')||'|' STRING2,
      '|'||regexp_replace(repeat_interval,'.*''([^'']+)''.*','-\1-')||'|' STRING3,
      '|'||regexp_replace(repeat_interval,'(.*)','-\1-')||'|' STRING4
    FROM  t;
    
    String1: 
    |-THURSDAY-|
    String2:
    |-THURSDAY-)x|
    String3:
    |-THURSDAY-|
    String4:
    |-WEEKLY(.417,'THURSDAY')x---|                                                                                                                                                                                                                                                                                        
  • 13. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
    jeneesh Guru
    Currently Being Moderated
    I was thinking in line of what Sven explained in his first reply..But at second look after reading all the comments, me too feel it like a bug..

    @Alberto: (.*) works good confirms it as a bug I feel...
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points