1

I have the following expression that matches the string listed when I test it using Online Regex Tester (https://regex101.com/).

Expression: 

^(?:[A-Z]{3}[0-9]{3}[a-z]{1}\([0-9]{1,3}\-[0-9]{1,3}\))\+\s?(?:[A-Z]{2}\((?:[0-9]{1,3}((?:\-[0-9]{1,3}(?:\s?\,\s?)?))?)+\)(?:\s?\+\s?)?)*$

String Input:

ATL107j(1-132)+XD(133-156)+SP(157-288)+XD(289-432)

If I use:

SELECT 1 FROM DUAL WHERE REGEXP_LIKE([String Input], [Expression])

I get no rows. I am not sure what is wrong with the expression as it relates to REGEXP_LIKE. I am using Oracle 11gR2.

Any help is appreciated. Thank you.

anteater
  • 55
  • 1
  • 7
  • 1
    when i tried the expression with input on the test site, i get no match..perhaps you missed something? – cableload Apr 26 '16 at 15:21
  • 1
    Same as above... no match! – Max Apr 26 '16 at 15:22
  • 1
    Oops. Sorry. Copied the incorrect expression. Here is the correct one that should work with this string input on the test site. Thanks for checking it out: ^(?:[A-Z]{3}[0-9]{3}[a-z]{1}\([0-9]{1,3}\-[0-9]{1,3}\))\+\s?(?:[A-Z]{2}\((?:[0-9]{1,3}((?:\-[0-9]{1,3}(?:\s?\,\s?)?))?)+\)(?:\s?\+\s?)?)*$ – anteater Apr 26 '16 at 15:25
  • 1
    still no match...what about the input? – cableload Apr 26 '16 at 15:26
  • 1
    I see what is happening. When I paste my expression here, it changes it. – anteater Apr 26 '16 at 15:27
  • [CODE] ^(?:[A-Z]{3}[0-9]{3}[a-z]{1}\([0-9]{1,3}\-[0-9]{1,3}\))\+\s?(?:[A-Z]{2}\((?:[0-9]{1,3}((?:\-[0-9]{1,3}(?:\s?\,\s?)?))?)+\)(?:\s?\+\s?)?)*$ [/CODE] – anteater Apr 26 '16 at 15:30
  • ^(?:[A-Z]{3}[0-9]{3}[a-z]{1}\([0-9]{1,3}\-[0-9]{1,3}\))\+\s?(?:[A-Z]{2}\((?:[0-9]{1,3}((?:\-[0-9]{1,3}(?:\s?\,\s?)?))?)+\)(?:\s?\+\s?)?)*$ I indented the expression 4 spaces. – anteater Apr 26 '16 at 15:32
  • Hmmm. Even indent didn't work! Not sure how to paste the expression here without it being altered! – anteater Apr 26 '16 at 15:35
  • what you want to do is edit your initial post and do the following: delete your initial regex, paste new regex, select your regex in full and then click the { } on the formatting options. – cableload Apr 26 '16 at 15:36
  • To show "code" in a comment (not in a Question or an Answer), use the back-single-quote (on most keyboards it's in the upper-left corner), the symbol ` before and after your code. –  Apr 26 '16 at 15:38
  • Thanks everyone. I did what cableload suggested and modified the post. I believe the expression is intact now. – anteater Apr 26 '16 at 15:39
  • After some research I found that dropping the capture group from the expression does the trick for REGEXP_LIKE. I am not sure if there is a way to tell Oracle to ignore capture groups, but I am able to run it in Oracle now. Thank you all for helping. – anteater Apr 26 '16 at 16:21
  • Ha! I guess we were experimenting at the same time (see my Answer). Oracle DOES support "capture groups" - the syntax only requires the use of parentheses, there is no ?: right after the ( –  Apr 26 '16 at 16:24

1 Answers1

1

What is ?: right after an open parenthesis, as in (?: ... ) ? That is not in the Oracle flavor of regex; if you remove all the ?: pairs, the query will return one row. In Oracle, the ?: are taken literally:

SQL> select 1 from dual where regexp_like('?:', '(?:)');

         1
----------
         1
1 row selected.

You must be using a regular expression feature from a different flavor, not supported by Oracle.

  • Thanks mathguy. I found the same thing. Capture groups don't seem to be supported by Oracle. Thanks again. – anteater Apr 26 '16 at 16:26
  • Actually yes, Oracle does support capture groups, just with a different syntax. `select regexp_substr('abc-02-e', '^([a-z]+)-(\d+)-.*$', 1, 1, null, 2) from dual;` returns the second capture group, '02' (see the documentation for regexp; here the last argument, 2, indicates which capture group should be returned). –  Apr 26 '16 at 16:37
  • Ah! Awesome, Thanks again! – anteater Apr 26 '16 at 20:00