I think i found it in PLVision Library, it has great utility functions
for string manipulation like string tokenizer
DA Morgan wrote:
> Anil G wrote:
> > 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
>
> There may be a simpler solution.
>
> SELECT TRANSLATE(REPLACE('(G10)-(G1+G2*G11*G12*G22)','G',',G'),
> '1()+-/*', '1')
> FROM dual;
>
> Works in any recent version of Oracle.
>
> And if the leading comma bothers you ... you could always throw in
> an LTRIM.
> --
> 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 - 23:12:37 CDT
