Very nice, Thanks Jaromir. The all_occurrence is nice to know. Mike On Sun, Oct 25, 2009 at 12:31 PM, Jaromir D.B. Nemec <jaromir@xxxxxxxxxxxx>wrote: > 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 http://forums.oracle.com/forums/thread.jspa?threadID=668970 for > further details of the all_occurences solution. > > Regards, > > Jaromir > > > ----- Original Message ----- > *From:* Jared Still <jkstill@xxxxxxxxx> > *To:* michaeljmoore@xxxxxxxxx > *Cc:* oracle-l@xxxxxxxxxxxxx > *Sent:* Friday, October 23, 2009 8:10 PM > *Subject:* Re: Need regexp help (seemingly simple problem) > > Mike, > > 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 ordevdb01.radisys.com - js001292@dv11 SQL> / > > SOURCE TARGE TARGE > -------------- ----- ----- > 2B4a12345Z2lr7 12345 12345 > > 1 row selected. > > > Jared Still > Certifiable Oracle DBA and Part Time Perl Evangelist > Oracle Blog: http://jkstill.blogspot.com > Home Page: http://jaredstill.com > > > > On Fri, Oct 23, 2009 at 11:01 AM, Michael Moore > <michaeljmoore@xxxxxxxxx>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. >> >> Thanks, >> Mike >> > >