Re: Need regexp help (seemingly simple problem)

From: Rich Jesse <>
Date: Fri, 23 Oct 2009 13:53:27 -0500 (CDT)
Message-ID: <>

Hey Mike,

> First of all I would like to say that finding detailed information about
> Oracle's implementation of regular expressions is next to impossible. Either
> that, or I am looking in all the wrong places. So, links to any complete
> online documentation would be appreciated. I found plenty of 'basic
> explanation' but there has got to be more.

This site might help:

...although it's lacking in version-specific information about what Oracle's implemented. There's also a link at the bottom of the page for a book from Amazon.

> I could easily use INSTR and SUBSTR to do this, but my goal is to solve the
> problem using a single REGEXP_SUBSTR statement. Also, I have used the
> pattern 'a[[:digit:]]+' which results in 'a12345'. This is almost right, but
> I don't want the 'a' to be returned.

I don't think that's possible with Oracle's regex. It's possible in Perl, but only in 5.10 and up from what I've found. This is as close as I could get:

select regexp_substr('2B4a12345Z2lr7','[[:digit:]]+',

       regexp_instr('2B4a12345Z2lr7','a')) from dual;

Yes, the REGEXP_INSTR is overkill as INSTR would work just as well and is most likely faster for this particular use.

Here's the doc that describes a possible "true" answer in regex, although it does not appear to be possible in Oracle's regex.

HTH! Rich

Received on Fri Oct 23 2009 - 13:53:27 CDT

Original text of this message