Re: Regular Expression Question

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 30 Jan 2007 06:45:52 -0800
Message-ID: <1170168352.145605.317320@k78g2000cwa.googlegroups.com>

On Jan 30, 5:59 am, Maxim Demenko <mdeme..._at_arcor.de> wrote:
> 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.comFor 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 OTNhttp://forums.oracle.com/forums/thread.jspa?threadID=435109http://forums.oracle.com/forums/thread.jspa?threadID=430647http://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- Hide quoted text -- Show quoted text -

Since the OP mentioned the regular expression functions by name he or she has access to 10g. I just want to point out that the substr and instr functions can be used to get the same result on prior versions:

UT1 > @t7
UT1 > set echo on
UT1 >
UT1 > select substr('select /* test string */ col1 ',1,
  2          instr('select /* test string */ col1 ','/*',1) - 1)||
  3         substr('select /* test string */ col1 ',
  4          instr('select /* test string */ col1 ','*/',1) + 2,
  5         length('select /* test string */ col1 ') -
  6          instr('select /* test string */ col1 ','*/') )

  7 from sys.dual
  8 /

SUBSTR('SELEC

---

select col1

HTH -- Mark D Powell -- Received on Tue Jan 30 2007 - 08:45:52 CST