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

Wait a sec, this reference in the Perl-Influenced Extensions in Oracle Regular Expressions part says it is OK to use lazy quantifiers. What result do you expect for the 2nd input? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jul 19, 2016 at 12:06
I think that maybe I may be a bit off here. I wanted to select 123 from those "abc_<something>: 123" strings. Am I correct in using regexp_replace for that? — devoured elysium
– devoured elysium, Commented Jul 19, 2016 at 12:15
Maybe .*abc[^:]*: *([^,]*),.* will help? You did not match the whole rest of the string after the comma. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jul 19, 2016 at 12:19
Or SELECT regexp_substr(' "abc_78": 123, "def_79": [', 'abc_[^:]*:\s*(\d+)', 1,1,NULL,1) from dual; — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jul 19, 2016 at 12:25

Michał M · Accepted Answer · 2016-07-19 12:17:32Z

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;

answered Jul 19, 2016 at 12:17

Michał M

6185 silver badges14 bronze badges

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

3 Comments

devoured elysium Over a year ago

Yes, you're right. So I'll slightly rephrase my question. What I'm looking for is to find and print 123 in that string. What other SQL function (on Oracle, or standard if such exists) should I be using for that?

devoured elysium Over a year ago

I see, just one more thing. That last parameter, 2, seems kinda dodgy. Wouldn't there be a better of way, maybe using capture groups, to capture the 123?

Michał M Over a year ago

In this substr function which i write, 2 means 2nd occurence in string (in this case 123) 1st is 78, 2nd is 123. Of course there are better ways, but without dev environment (sqldeveloper f.e.) This is all i can give.

Collectives™ on Stack Overflow

Regex_replace not replacing correctly in Oracle SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related