Oracle REGEX to match two consecutive 'a'-CodePudding

I am trying to replace two consecutive aa in the oracle database using REGEXP_REPLACE.

The SQL I tried so far is below

   select regexp_replace('aab','(a)(a)|(a)(a)','\1 \2 \3') from dual;

The expected result is a ab and the actual result is a a b.

Basically, I want (a)(a) to match with two consecutive aa.What is the regular expression I must use?

Please note I am using this particular SQL as a workaround if there are more than three or more consecutive a

select regexp_replace('aaa','(a)(a)|(a)(a)','\1 \2 \3') from dual;

gives me the result a a a which is expected.

CodePudding user response：

Since Oracle regex engine does not support lookarounds, all you can use is

REGEXP_REPLACE(REGEXP_REPLACE(col, '(a)(a)', '\1 \2'), '(a)(a)', '\1 \2')

This effectively adds a space between one a and another a.