Need regexp help (seemingly simple problem)

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Fri, 23 Oct 2009 11:01:31 -0700
Message-ID: <26fdee6e0910231101p4560cf58h4266aff99ffdc38a_at_mail.gmail.com>

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.

Here is the specific problem I am trying to solve using REGEXP_SUBSTR. I have a string that might look something like this:

'2B4a12345Z2lr7' . I want to extract the 12345. In words, find the first
letter 'a' and return all the digits up to the letter Z.

There can be any number of random non-'a' characters prior to the initial
'a' and likewise after the letter Z. In other words, in my example, '2B4'
just represents some random sequence of characters, as does '2lr7'.

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.

Thanks,
Mike

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 23 2009 - 13:01:31 CDT