Regex_replace not replacing correctly in Oracle SQL

Question

I'm trying to get the following Regex expression to work with Oracle SQL:

select regexp_replace('    "abc_78": 123, ', '.*?abc.*?: (.*?),.*', '\1') from dual;

select regexp_replace('    "abc_78": 123, "def_79": [', '.*?abc.*?: (.*?),', '\1') from dual;

The first one returning

"123"

(which I deem correct) while the second one returning

"123 "def_79": ["

.

What's the issue at stake here? A bad regex or some weird functioning of Oracle? The regex seems to work well when tried against Sublime Text. I'm running this query directly off Oracle SQL Developer.

Thanks

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

It's replacing correctly.

select regexp_replace('    "abc_78": 123, "def_79": [', '.*?abc.*?: (.*?),', '\1') from dual;

First: It (regex engine) finds '"abc_78": 123' where 123 is group $1. Then it replaces 'abc_78: 123' with group 1 which is 123.

And you have little diffrence in those regex patterns like:

'.*?abc.*?: (.*?),.*', '\1') from dual;
'.*?abc.*?: (.*?),', '\1') from dual;

missing .* in 2nd pattern.

If You want to retrieve 123 from this strings, is better to use regex_substr

select regexp_substr('    "abc_78": 123, ','\d+',1,2) from dual;

Regex_replace not replacing correctly in Oracle SQL

Sign up