Re: Regular Expression Question

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Tue, 30 Jan 2007 11:59:20 +0100
Message-ID: <45BF2508.1010004@arcor.de>

Vladimir M. Zakharychev schrieb:
>
> On Jan 30, 3:51 am, "m.t" <m..._at_matelot.com> wrote:

>> I have comments like this "/* this is comment */"
>> is dynamic SQL string.
>>
>> Please show me how I can use Regular Expression to
>> replace the comments with NULL.
>>
>> THANKS !
>>
>> --
>> 10gR2

>
> SQL> select regexp_replace( 'select /* comments */ * from /* more
> comments */ dual;', '/\*.*?\*/', null) from dual;
>
> REGEXP_REPLACE('SELEC
> ---------------------
> select * from dual;
>
> The key is non-greedy matching modifier ( ? after the .*, ) which is
> only available since 10g R2 (10.2). In 10g R1 (10.1), which doesn't
> support Perl-influenced extensions, RE matching is always greedy (that
> is, it will attempt to match the longest possible substring, not the
> shortest possible,) and I didn't find a way to make it non-greedy, so
> the above solution is only for 10.2. Since you seem to be running this
> release, should work for you.
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com
>

For nongreedy simulation on 10gR1 this can work: with t as (select 'select /* comments */ * from /* more comments */ dual;' s from dual)
select
regexp_replace(s, '(/\*[^*]*\*/)',NULL)
from t;

There was a very good thread about regexp authored by CD on the OTN

http://forums.oracle.com/forums/thread.jspa?threadID=435109
http://forums.oracle.com/forums/thread.jspa?threadID=430647
http://forums.oracle.com/forums/thread.jspa?threadID=427716

where he discussed differences between 10gR1 and 10gR2 among other things, buttom line - an excellent introduction for Oracle regexp.

Best regards

Maxim Received on Tue Jan 30 2007 - 04:59:20 CST