Re: Search Replace String function

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 9 Aug 2006 12:16:07 -0700
Message-ID: <1155150967.444153.7410@75g2000cwc.googlegroups.com>

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 Wed Aug 09 2006 - 14:16:07 CDT