RE: regular expression to get ORA- errors out of text
- From: "Tanel Poder" <tanel@xxxxxxxxxx>
- To: <goryunov.oracle.l@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 14 Feb 2009 21:25:42 -0700
Hi Andrey,
SQL> define str=" ORA-00001 ora-0002 ora ora-003 aasdlkja alksdj
alksdj laskdj ORA-04 aaaa a a a aora-5 "
SQL>
SQL> select regexp_substr('&str', 'ora-[[:digit:]]{1,5}', 1, level, 'i')
2 from dual
3 connect by regexp_substr('&str', 'ora-[[:digit:]]{1,5}', 1, level, 'i')
is not null
4 /
REGEXP_SUBSTR('ORA-00001ORA-0002ORAORA-003AASDLKJAALKSDJALKSDJLASKDJORA-04AA
AAAAAAORA-5','ORA-[[:DIGIT:
----------------------------------------------------------------------------
---------------------------
ORA-00001
ora-0002
ora-003
ORA-04
ora-5
5 rows selected.
SQL>
--
Tanel Poder
http://blog.tanelpoder.com
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Andrey Goryunov
Sent: 14 February 2009 20:34
To: oracle-l@xxxxxxxxxxxxx
Subject: regular expression to get ORA- errors out of text
Hi All,
what regular expression would you suggest to get all ORA- errors (with from
1 to 5 numbers)
out of some text like this:
' ORA-00001 ora-0002 ora ora-003 aasdlkja alksdj alksdj laskdj
ORA-04 aaaa a a a aora-5 '
I am trying to figure out how to use
regexp_replace to replace all characters around ora-... errors to get
output:
'ORA-00001 ora-0002 ora-003 ORA-04 ora-5'
but can't find appropriate expression so far:
with s1 as (select ' ORA-00001 ora-0002 ora ora-003 aasdlkja
alksdj alksdj laskdj ORA-04 aaaa a a a aora-5 ' s from dual)
select regexp_replace(s, '...', '...', 1, 0, 'i') from s1
/
--
Regards,
Andrey Goryunov
Other related posts:
