Re: Need regexp help (seemingly simple problem)

From: Jaromir D.B. Nemec <>
Date: Sun, 25 Oct 2009 20:31:51 +0100
Message-ID: <4D04978DA4B247ACADB560C0270907F1_at_ADLA>

Hi Michael,

small addendum to the Jared's solution - in case there can be more such digit sequences in the string

select '2B4a12345Z2la6789ZraxxxZyaZZa45aaZZZ7' source

  • want from 'a' to 'Z'
    , regexp_replace('2B4a12345Z2la6789ZraxxxZyaZZa45aaZZZ7','^.*a([[:digit:]]+)Z.*$','\1',1,1) last_occurence
    , regexp_replace('2B4a12345Z2la6789ZraxxxZyaZZa45aaZZZ7','^.*?a([[:digit:]]+)Z.*$','\1',1,1) first_occurence
    , regexp_replace('2B4a12345Z2la1234567ZraxxxZyaZZaa45aZZZ7','a([[:digit:]]+)Z|a|[^a]*','\1 ',1,0) all_occurences
    from dual ; SOURCE LAST_OCCURENCE FIRST_OCCURENCE ALL_OCCURENCES ------------------------------------- -------------- --------------- --------------------------- 2B4a12345Z2la6789ZraxxxZyaZZa45aaZZZ7 6789 12345 12345 1234567

1 rows selected

See for further details of the all_occurences solution.



  • Original Message ----- From: Jared Still To: Cc: Sent: Friday, October 23, 2009 8:10 PM Subject: Re: Need regexp help (seemingly simple problem)


  The [:digit:] class actually need to be written as [[:digit:]].

  Here's an example using regexp_replace

  select '2B4a12345Z2lr7' source

  • '2B4a12345Z2lr7'
  • want from 'a' to 'Z' , regexp_replace('2B4a12345Z2lr7','^.*a([[:digit:]]+)Z.*$','\1',1,1) target , regexp_replace('2B4a12345Z2lr7','^.*a([0123456789]+)Z.*$','\1',1,1) target from dual /

  12:09:39 - js001292_at_dv11 SQL> /


  • ----- ----- 2B4a12345Z2lr7 12345 12345

  1 row selected.

  Jared Still
  Certifiable Oracle DBA and Part Time Perl Evangelist   Oracle Blog:   Home Page:

  On Fri, Oct 23, 2009 at 11:01 AM, Michael Moore <> wrote:

    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.


Received on Sun Oct 25 2009 - 14:31:51 CDT

Original text of this message