I'm trying to extract 456 from the string :123:456: as follows:
select regexp_substr(':123:456:', ':(\d+):', 1, 2, 'i', 1) from dual
However, this query returns null. What am I doing wrong?
asked Sep 5, 2012 at 16:14
Add a comment
|
2 Answers
This works fine:
select regexp_substr(':123:456:', '(\d+):', 1, 2, 'i', 1) from dual;
I think yours fails because the opening and closing colons won't get matched by both occurrences (because the first match is greedy).
select regexp_substr(':123:456:','[^:]+',2,2) from dual
-
1If this is substantially different from the accepted answer posted by Phil 10 months ago, please add some context explaining why. Commented Jul 9, 2013 at 13:58
-
This will match on
:123:abc:which doesn't sound desirable, but fail to match on::456:which might be.– MatCommented Jul 9, 2013 at 17:03