Oracle REGEXP_SUBSTR not working with my pattern

Question

I have the following query:

SELECT DISTINCT A.REZ FROM
(
  SELECT REGEXP_SUBSTR(P_EQUATION, '([A-Z|a-z|0-9]+)\{([0-9|\+|\-| |\*|\/\)\(]+)\}#([A-Z|a-z|0-9|_]+)#',1, LEVEL) AS REZ FROM DUAL
  CONNECT BY REGEXP_SUBSTR(P_EQUATION, '([A-Z|a-z|0-9]+)\{([0-9|\+|\-| |\*|\/\)\(]+)\}#([A-Z|a-z|0-9|_]+)#',1, LEVEL) IS NOT NULL
) A;

If I supplied the following input:

P_EQUATION := 'A123{(01+02)*2}#ACCOUNT_BALANCE# + B123{(20+10)/20}#ACCOUNT_BALANCE#';

It gives me the following:

REZ
-------------------------------------
A123{(01+02)*2}#ACCOUNT_BALANCE#
B123{(20+10)/20}#ACCOUNT_BALANCE#

But, although the minus sign is included in the pattern, if I have added it inside the curly brackets, it will not recognize the text anymore as a match!

ex:

P_EQUATION := 'A123{(01-02)*2}#ACCOUNT_BALANCE#';

I'm not able to find a solution to this, it is freaking me out, especially, when I tried to match the minus sign alone it works, if I tried to match digits alone it also works :(

leave aside everything..why are you using | in character class? [A-Za-z0-9]+ alone will work fine — rock321987
– rock321987, Commented May 3, 2016 at 12:13
Oh, I see, I will fix this later when I know what's wrong with the pattern in the case I described — sameh.q
– sameh.q, Commented May 3, 2016 at 12:21

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Oracle appears to be using POSIX style regexes: https://docs.oracle.com/cd/B12037_01/server.101/b10759/ap_posix001.htm#i690819

The backslash is NOT a metacharacter in a POSIX bracket expression. So in POSIX, the regular expression [\d] matches a \ or a d

> http://www.regular-expressions.info/posixbrackets.html

The backslashes are probably messing it up, and they're not necessary. You also don't realize that | is a literal inside a char class (which the comments also pointed out). I have fixed these problems, and I moved the - to the start of the char class, which allows it to be interpreted as a literal.

Here you go:

([A-Za-z0-9]+)\{([-0-9+ */)(]+)\}#([A-Za-z0-9_]+)#

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 3, 2016 at 14:56

Laurel

6,23114 gold badges35 silver badges60 bronze badges

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

1 Comment

sameh.q Over a year ago

That worked! thank you. I have tried to remove all the |, but didn't work neither before, the minus sign position in the pattern was kept the same, that's why it wasn't working

msheikh25 · Accepted Answer · 2016-05-03 15:10:13Z

Couldn't quite figure out the issue with your code but here is one way to do it:

with temp as
(
  select 'A123{(01+02)*2}#ACCOUNT_BALANCE# + B123{(20+10)/20}#ACCOUNT_BALANCE#' P_EQUATION from dual union all
  select 'A123{(01-02)*2}#ACCOUNT_BALANCE#' P_EQUATION from dual
)

SELECT DISTINCT A.REZ FROM
(
  SELECT REGEXP_SUBSTR(P_EQUATION, '[[:alpha:]]+[[:digit:]]+{\([[:digit:]]+\S[[:digit:]]+\)\S[[:digit:]]+}#[[:alpha:]]+_[[:alpha:]]+#',1, LEVEL) AS REZ FROM temp
  CONNECT BY REGEXP_SUBSTR(P_EQUATION, '[[:alpha:]]+[[:digit:]]+{\([[:digit:]]+\S[[:digit:]]+\)\S[[:digit:]]+}#[[:alpha:]]+_[[:alpha:]]+#',1, LEVEL)  IS NOT NULL
) A;

OUTPUT:

REZ                                                                                                                                                                                                                                                                            
---------------------------------------
B123{(20+10)/20}#ACCOUNT_BALANCE#                                                                                                                                                                                                                                                
A123{(01-02)*2}#ACCOUNT_BALANCE#                                                                                                                                                                                                                                                 
A123{(01+02)*2}#ACCOUNT_BALANCE#

Collectives™ on Stack Overflow

Oracle REGEXP_SUBSTR not working with my pattern

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related