Re: Need regexp help (seemingly simple problem)
- From: "Jaromir D.B. Nemec" <jaromir@xxxxxxxxxxxx>
- To: <jkstill@xxxxxxxxx>, <michaeljmoore@xxxxxxxxx>
- Date: Sun, 25 Oct 2009 20:31:51 +0100
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
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
Other related posts:
