Oracle RegEx Matching

Question

Oracle select statement having REGEXP_LIKE in where clause is failing randomly with the error ORA-12726: unmatched bracket in regular expression. When I re-run the query it works perfectly. I have validated all regex in DB and all are syntactically correct. Any thoughts why it is failing and why it is returning the results when I re-run the query.

 SELECT
     r.*
 FROM
     routeuser.ahc_b2b_route r
 WHERE
     r.producer ='Facets'
     AND REGEXP_LIKE ('PCP2AAME.2A.zip.end', r.filemask, 'i' )
     ORDER BY
     length(r.filemask) DESC, r.filemask DESC

Result:

Resulting RegEx ^PCP(37|41|47|57|2A)(AME|KEY)[.](37|41|47|57|2A)[.]zip.end$

Appreciate any help with this.

Gary_W · Accepted Answer · 2018-09-12 13:31:01Z

1

I believe you are using [.] to get a literal dot? Try escaping using \. instead and see if your problem goes away.

^PCP(37|41|47|57|2A)(AME|KEY)\.(37|41|47|57|2A)\.zip\.end$

answered Sep 12, 2018 at 13:31

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GSR Over a year ago

Thank you will try. Tested the new one that you suggested and it worked.

Collectives™ on Stack Overflow

Oracle RegEx Matching

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related