Re: Search Replace String function

Thanks for reply,

But how do it can be done on 9i? REPLACE function does not solve this problem...
meaning,

If expression is like : (G1 + G2 + G11) * G12 * G22

Then replace will have issues with G11 and G22 due to replace of G1 and G2

Vladimir M. Zakharychev wrote:
> Anil G wrote:
> > Does oracle provides search replace string functionality?
> >
> > e.g. lets say i have data into column as '(A + B + C) - (D + E)'
> > I would like to have following : (
> > NVL(SUM(DECODE(b.lookup_code,'A',a.q_qty,0)),0) +
> > NVL(SUM(DECODE(b.lookup_code,'B',a.q_qty,0)),0) +
> > NVL(SUM(DECODE(b.lookup_code,'C',a.q_qty,0)),0)
> > ) -
> > (
> > NVL(SUM(DECODE(b.lookup_code,'D',a.q_qty,0)),0) +
> > NVL(SUM(DECODE(b.lookup_code,'E',a.q_qty,0)),0)
> > )
> >
> > In this case only thing needs to be done is search replace all values
> > with
> > NVL(SUM(DECODE(b.lookup_code,??,a.q_qty,0)),0)
>
> If you're on 10g, then consider this:
>
> select regexp_replace(
> '(A+B+C)-(D+E)'
> ,'([A-Z]{1})'
> ,'NVL(SUM(DECODE(b.lookup_code,''\1'',a.q_qty,0)),0)'
> )
> from dual;
>
> Of course, the regexp wouldn't be that simple if instead of single
> uppercase letter your codes are various strings, but still doable. For
> example, if your variable placeholders are prefixed with some special
> character, like '#', then the regexp isn't a lot more complex:
> '#([[:alpha:]]+)'.
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com
Received on Thu Aug 10 2006 - 22:28:34 CDT