Re: Need regexp help (seemingly simple problem)

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Fri, 23 Oct 2009 12:19:28 -0700
Message-ID: <26fdee6e0910231219v5632eaedx59ad045fb51e0bf9_at_mail.gmail.com>



Hi Jared,
Yeah, I figured that out about the double [[. Seems contrary to what the manual says, but there you have it. I didn't consider using regexp_replace. Cool. I'll play with it a bit.
Thanks!
Mike

On Fri, Oct 23, 2009 at 12:10 PM, Jared Still <jkstill_at_gmail.com> wrote:

> 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_at_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_at_gmail.com>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
>>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 23 2009 - 14:19:28 CDT

Original text of this message