This forum is currently locked. You can't register or post questions at this time. (read more)

Regular Expression issue

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

narendrareddy2414
Member
Posts: 14
Joined: Wed Jul 20, 2011 6:04 am

Regular Expression issue

Postby narendrareddy2414 » Wed Aug 29, 2012 12:04 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Regular Expression issue

Postby Tim... » Wed Aug 29, 2012 5:23 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

narendrareddy2414
Member
Posts: 14
Joined: Wed Jul 20, 2011 6:04 am

Re: Regular Expression issue

Postby narendrareddy2414 » Thu Aug 30, 2012 6:12 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Regular Expression issue

Postby Tim... » Thu Aug 30, 2012 6:35 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

narendrareddy2414
Member
Posts: 14
Joined: Wed Jul 20, 2011 6:04 am

Re: Regular Expression issue

Postby narendrareddy2414 » Thu Aug 30, 2012 6:49 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Regular Expression issue

Postby Tim... » Thu Aug 30, 2012 6:54 am

Hi.

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

narendrareddy2414
Member
Posts: 14
Joined: Wed Jul 20, 2011 6:04 am

Re: Regular Expression issue

Postby narendrareddy2414 » Thu Aug 30, 2012 6:57 am

Hi
Thanks a Ton Tim,
Will try that.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Regular Expression issue

Postby Tim... » Thu Aug 30, 2012 10:43 am

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

narendrareddy2414
Member
Posts: 14
Joined: Wed Jul 20, 2011 6:04 am

Re: Regular Expression issue

Postby narendrareddy2414 » Thu Aug 30, 2012 1:52 pm

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

:D

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Regular Expression issue

Postby Tim... » Thu Aug 30, 2012 2:43 pm

:)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 1 guest

cron