Regular Expression Difficulty

Question

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?

Answer 1

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).

Answer 2

select regexp_substr(':123:456:','[^:]+',2,2) from dual

• Aaron Bertrand: If this is substantially different from the accepted answer posted by Phil 10 months ago, please add some context explaining why.
• Mat: This will match on :123:abc: which doesn't sound desirable, but fail to match on ::456: which might be.