Regular Expression issue

[narendrareddy2414]

Hi Tim,
i'm using oracle10g version 10.2.0
OS Windows7
Have a requirement where in i need to replace the occurrence of numbers followed '%' sign into XX symbol in a given string which looks this '10% of CIF + 5% of CIF'.
Using the REG_EXP_REPLACE like this:

Code: Select all

SELECT REGEXP_REPLACE('10% of CIF + 5% of CIF','([[:digit:]]+\%?)','XX%',1,1) REGEXT_SAMPLE
FROM DUAL;
OUTPUT: XX% of CIF + 5% of CIF
SELECT REGEXP_REPLACE('10% of CIF + 5% of CIF','([[:digit:]]+\%?)','XX%',1,2) REGEXT_SAMPLE
FROM DUAL;
OUTPUT: 10% of CIF + XX% of CIF


This gives proper result when there are no decimal digits.
But even if the string contains numbers like this '10% of CIF + 5.5% of CIF' it should work.
Have tried changing the pattern as below, which doesn't solve the puporse

Code: Select all

SELECT REGEXP_REPLACE('10% of CIF + 5.5% of CIF','(([[:digit:]]|([[:digit:]].[[:digit:]]))+\%?)','XX%',1,2) REGEXT_SAMPLE
FROM DUAL;
OUTPUT:10% of CIF + XX%.5% of CIF


Please help me on this

[Tim...]

Hi.

For the string structure you are using, I think I would just do this,

Code: Select all

SELECT TRANSLATE('10% of CIF + 5.5% of CIF', '0123456789', 'XXXXXXXXXX')
FROM   dual;

TRANSLATE('10%OFCIF+5.5%
------------------------
XX% of CIF + X.X% of CIF

1 row selected.

SQL>

Cheers

Tim...

[narendrareddy2414]

Hi,
Thanks for your reply Tim,
But after conversion i should get output as XX% of CIF + XX% of CIF, instead of XX% of CIF + X.X% of CIF.
If we use translate it will translate all the numbers into X.
In the expressions we may get other numbers without %, those cases we should retain as it is.
Example: if the expression differs like this '10% of CIF + 5.5% of CIF + 10.1 USD per KG'
We need to change only where the numbers have % symbol and retain others as it is: 'XX% of CIF + XX% of CIF + 10.1 USD per KG'

[Tim...]

Hi.

OK. Not sure if you are asking me to do anything here, or are you saying your original solution is fine since you never need to deal with the "." after all?

Cheers

Tim...

[narendrareddy2414]

Hi,
No my original solution is not fine here.
Need an alternative how i can deal with "." in between numbers. to replace 5.5% with XX% instead of X.X%.
Tim please let me know if i'm confusing you.

[Tim...]

Hi.

Do what do you want here? To just remove the "." or truncate the decimal value?

Cheers

Tim...

[narendrareddy2414]

Hi
Thanks a Ton Tim,
Will try that.

[Tim...]

OK. Also not a question, so I don;t know if you want me to do anything...

Cheers

Tim...

[narendrareddy2414]

Hi Tim,
Have tried and got the solution as below and it serves my purpose.

Code: Select all

SQL>SELECT
    REGEXP_REPLACE('18.0% of CIF+10.5% of cif+0.5 kg per', '((\d+)\%|(\d+)\.(\d+)\%)', 'XX%',1,1) REG_REPLACE_TEST
    FROM dual;
REG_REPLACE_TEST
-----------------------
XX% of CIF+10.5% of cif+0.5 kg per
SQL>                     
SELECT
    REGEXP_REPLACE('18.0% of CIF+10.5% of cif+0.5 kg per', '((\d+)\%|(\d+)\.(\d+)\%)', 'XX%',1,2) REG_REPLACE_TEST
    FROM dual;
REG_REPLACE_TEST
-----------------------
18.0% of CIF+XX% of cif+0.5 kg per


[Tim...]

Cheers

Tim...