1 2 Previous Next 25 Replies Latest reply: Dec 6, 2012 6:13 AM by chris227 RSS

    REGEXP_REPLACE - Is this strange Or Am I Missing something?

    jeneesh
      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
          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
            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
              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
                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
                  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
                    Just needed a coffee...Clear now..
                    • 7. Re: REGEXP_REPLACE - Is this strange Or Am I Missing something?
                      Manik
                      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
                        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.
                          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
                            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
                              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.
                                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
                                  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