Re: Search Replace String function

From: Anil G <anil.gothal_at_gmail.com>
Date: 11 Aug 2006 10:37:30 -0700
Message-ID: <1155317850.390692.153570@i3g2000cwc.googlegroups.com>

Thanks you for your responses, Here is details about the problem: Oracle 9i,

I have expession like : (G10)-(G1+G2*G11*G12*G22)

which is dynamically defined by user and i need to parse and replace each variable so that outcome would be :

(
NVL(SUM(DECODE(b.lookup_code,'G10',a.e_qty,0)),0) )
- (

NVL(SUM(DECODE(b.lookup_code,'G1',a.enrollment_qty,0)),0) +
NVL(SUM(DECODE(b.lookup_code,'G2',a.enrollment_qty,0)),0) *
NVL(SUM(DECODE(b.lookup_code,'G12',a.enrollment_qty,0)),0) *
NVL(SUM(DECODE(b.lookup_code,'G22',a.enrollment_qty,0)),0)

)

I had tried with REPLACE logic, but it has troubles for replacing G11,G10 etc, it treats sustring match rather than word match...

Let me know,

Thanks in advance,

DA Morgan wrote:
> Martin T. wrote:
> > Anil G wrote:
> >>>> e.g. lets say i have data into column as '(A + B + C) - (D + E)'
> >>>> I would like to have following : (
> >> 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
> >>
> > In this case regular expressions will be the best way. (except that it
> > does not come out of the box in 9i)
> > You could use a stored Java procedure to do the parsing/replacing (no
> > java regexp out of the box on 9i either, since it uses Java 1.3, I
> > think)
> >
> > best,
> > Martin

>

> In 9i some Regular Expression capability can be found in the OWA_MATCH
> built-in package. For example:

>

> CREATE OR REPLACE FUNCTION owa_match(phone# VARCHAR2)
> RETURN BOOLEAN IS

>

> BEGIN
> RETURN owa_pattern.MATCH(phone#,'^[0-9]{3}-[0-9]{3}-[0-9]{4}$');
> END owa_match;
> /
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
Received on Fri Aug 11 2006 - 12:37:30 CDT