Re: Search Replace String function

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