Re: Regular Expression Question

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 30 Jan 2007 00:45:06 -0800
Message-ID: <1170146706.392729.214740@q2g2000cwa.googlegroups.com>

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 Received on Tue Jan 30 2007 - 02:45:06 CST