SQL Question
Regex_replace not replacing correctly in Oracle SQL
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""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
Answer
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;
Source (Stackoverflow)