This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Dec 30, 2012 5:22 AM by chris227 Go to original post RSS
  • 15. Re: Reg : Regexp's back reference --
    padders Pro
    Currently Being Moderated
    I was trying to use this REGEXP_REPLACE approach to find multiple substrings but it is failing to match as expected if the string contains a linefeed. I'm presuming this is because the metacharacter '.' does not match linefeed by default.

    I see that there is an option on REGEXP_REPLACE to have '.' match a linefeed but in order to pass this parameter you have to specify position and occurrence which prevents you matching all occurrences.

    I got around this by specifying '(.|[[:space:]]*?)' in the regexp (doesn't '\n' match linefeed?) but [[:space:]] obviously isn't an exact match and I can't help wondering if I am missing a simpler or better way to do this.
  • 16. Re: Reg : Regexp's back reference --
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    padders wrote:
    I was trying to use this REGEXP_REPLACE approach to find multiple substrings but it is failing to match as expected if the string contains a linefeed. I'm presuming this is because the metacharacter '.' does not match linefeed by default.
    This is getting really far away from OPs question. Whenever you have you own question, please start your own thread. Include a little sample data (CREATE TABLE and INSERT statements), the results you want from that data, your best attempt at a solution, and your Oracle version (e.g. 11.2.0.1.0). See the forum FAQ {message:id=9360002}
    This is for your benefit. Not too many people are going to read a thread that already has 15 replies, and is already marked "Answered". How many replies did you get in the first 4 hours after you postd your message? The median response time for a new question on this forum is under 10 minutes.
    I see that there is an option on REGEXP_REPLACE to have '.' match a linefeed but in order to pass this parameter you have to specify position and occurrence which prevents you matching all occurrences.

    I got around this by specifying '(.|[[:space:]]*?)' in the regexp (doesn't '\n' match linefeed?) but [[:space:]] obviously isn't an exact match and I can't help wondering if I am missing a simpler or better way to do this.
    It works for me. Here, str is 1 row, with 3 linefeeds:
    STR                            IN_PARENS
    ------------------------------ ------------------------------
    Twas the (11)                  (11)(22)(33)
    night before
    Christmas (22) and (33) all
    through the house
  • 17. Re: Reg : Regexp's back reference --
    Solomon Yakobson Guru
    Currently Being Moderated
    Frank Kulash wrote:
    It works for me. Here, str is 1 row, with 3 linefeeds:
    Didn't work for me:
    SQL> select  *
      2    from  v$version
      3  /
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    
    SQL> with t as (
      2  select 'Twas the (11) night before Christmas (22) and (33) all through the house' str from dual
      3  )
      4  select  str,
      5  REGEXP_REPLACE(str,'.*?((\(\d\d\))|$)','\1') AS in_parens
      6  from t
      7  /
    
    STR                            IN_PARENS
    ------------------------------ --------------------
    Twas the (11) night before Chr (11)(22)(33)
    istmas (22) and (33) all throu
    gh the house
    
    
    SQL> with t as (
      2  select 'Twas the (11)
      3  night before
      4  Christmas (22) and (33) all
      5  through the house' str from dual
      6  )
      7  select  str,
      8  REGEXP_REPLACE(str,'.*?((\(\d\d\))|$)','\1') AS in_parens
      9  from t
     10  /
    
    STR                            IN_PARENS
    ------------------------------ --------------------
    Twas the (11)                  (11)
    night before                   night before
    Christmas (22) and (33) all    (22)(33) all
    through the house
    
    
    SQL>
    SY.
  • 18. Re: Reg : Regexp's back reference --
    padders Pro
    Currently Being Moderated
    This is getting really far away from OPs question
    Exactly the same question with a linefeed in the string is 'really far away' from the OPs question?

    You're right that I didn't post a proper example. Thanks for the marketing brochure though, I've only been here 15 years :-D
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> SELECT str,
      2         REGEXP_REPLACE (str,
      3             '.*?((\(\d\d\))|$)', '\1') AS in_parens
      4  FROM  (SELECT 'today (12)123 is the ' ||
      5                   CHR (10) ||
      6                   '(45)456 wednesday' str
      7         FROM   DUAL);
    
    STR                                     IN_PARENS
    --------------------------------------- --------------------
    today (12)123 is the                    (12)123 is the
    (45)456 wednesday                       (45)
    
    SQL>
  • 19. Re: Reg : Regexp's back reference --
    APC Oracle ACE
    Currently Being Moderated
    padders wrote:
    Thanks for the marketing brochure though, I've only been here 15 years :-D
    To be fair, Padders, you now have another account with a newbie-ish rating.


    Obviously I know who you are but perhaps you're not as infamous famous as you ought to be ;)

    Cheers, APC
  • 20. Re: Reg : Regexp's back reference --
    Solomon Yakobson Guru
    Currently Being Moderated
    padders wrote:
    Exactly the same question with a linefeed in the string
    Issues are:

    a) $ matches the end of any line the source string
    b) by default dot (.) doesn't match newline character

    So solution is to specify match parameter 'n' which allows dot to match the newline character:
    with t as (
    select 'Twas the (11)
    night before
    Christmas (22) and (33) all
    through the house' str from dual
    )
    select  str,
    REGEXP_REPLACE(str,'.*?((\(\d\d\))|$)','\1',1,0,'n') AS in_parens
    from t
    /
    
    STR                            IN_PARENS
    ------------------------------ --------------------
    Twas the (11)                  (11)(22)(33)
    night before
    Christmas (22) and (33) all
    through the house
    
    
    SQL>
    SY.
  • 21. Re: Reg : Regexp's back reference --
    padders Pro
    Currently Being Moderated
    Hehe it's OK. Frank deserves all the credit - his contribution here is beyond compare.

    And I did bump (I believe the term is 'necro') this thread rather late and without a good example.
  • 22. Re: Reg : Regexp's back reference --
    padders Pro
    Currently Being Moderated
    Nice, I should have read documentation fully regarding the 'occurrence' parameter, didn't realise zero was an option.
    If you specify 0, then Oracle replaces all occurrences of the match.
  • 23. Re: Reg : Regexp's back reference --
    Solomon Yakobson Guru
    Currently Being Moderated
    padders wrote:
    If you specify 0, then Oracle replaces all occurrences of the match.
    And this is what I was referring to in my reply to ranit B. Oracle seems inconsistent. It allows "all occurrences" in regexp_replace but not in regexp_substr.

    SY.
  • 24. Re: Reg : Regexp's back reference --
    ranit B Expert
    Currently Being Moderated
    Hi Padders,

    Were you previously present here in OTN?
    Then, why you are a Newbie now?

    Just For Fun -
    And then i guess, you would have touched the upper point margin of 'Guru' level and then their points would have got rest to 0 again. ;-)
    Implies - their Points are stored in some Signed variable.
  • 25. Re: Reg : Regexp's back reference --
    chris227 Guru
    Currently Being Moderated
    In my opinion it would be preferable to avoid constructs like .*? if possible, for several reasons, e.g. efficiency.
    In this case this would mean
    with t as (
    select 'Twas the (11)
    night before
    Christmas (22) and (33) all
    through the house' str from dual
    )
    
    select  str,
    REGEXP_REPLACE(str,'[^(]*((\(\d\d\))|$)','\1',1,0,'n') AS in_parens
    from t
1 2 Previous Next

Legend

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