CODEWITHSUNDEEP
regexplsql
GSR
GSR

Reputation: 73

Oracle RegEx Matching

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.

Upvotes: 0

Views: 184

Answers (1)

Gary_W
Gary_W

Reputation: 10360

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$

Upvotes: 1

Related Questions