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;
"123"
"123 "def_79": ["
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;