The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Thanks for the question, AnoopSagar.

Asked: April 15, 2010 - 2:00 am UTC

Last updated: April 16, 2010 - 10:04 am UTC

Version: 8.0.6.0.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have a requirement wherein I have to convert a string that has mathematical expression in it into number output.
E.g., the string can be '5/100*300'.
How to convert this into number. The number output of this example should be 15. How to achieve this? Is there a function in SQL to achieve this?

Thanks & Regards,
Anoop

and Tom said...

You would use dynamic SQL, however - you must be very very very careful to NOT introduce sql injection bugs

http://www.google.com/search?q=sql+injection

into your application.

if you are really 8.0 - read about dbms_sql (lots of examples on this site), if you are really using something more current - read about execute immediate (again, hundreds of examples on this site)

And do NOT plan on doing this dozens of times - if this is something you have to do once in a long while - ok, if this is an ongoing "we do this a million times a day" - do not do this in SQL - write a parser or use some API in your client application to do this.

Rating

(2 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

if on 11 you could

Sokrates, April 16, 2010 - 1:49 am UTC

sql > select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

sql > create function
  2    eval_simple_arithmetic_expr(vexpr in varchar2)
  3    return number
  4  is
  5  evaluated number;
  6  begin
  7  select
  8    xmlquery(
  9    replace( vexpr, '/', ' div ')
 10       returning content
 11    ).getNumberVal()
 12  into evaluated
 13  from
 14  dual;
 15  return evaluated;
 16  end eval_simple_arithmetic_expr;
 17  /

Function created.

sql > select eval_simple_arithmetic_expr('5/100*300'), 5/100*300 from dual;

EVAL_SIMPLE_ARITHMETIC_EXPR('5/100*300')  5/100*300
---------------------------------------- ----------
                                      15         15

sql > select eval_simple_arithmetic_expr('1*2*3+4+5*6+7'), 1*2*3+4+5*6+7 from dual;

EVAL_SIMPLE_ARITHMETIC_EXPR('1*2*3+4+5*6+7') 1*2*3+4+5*6+7
-------------------------------------------- -------------
                                          47            47

sql > select eval_simple_arithmetic_expr('193 mod 47'), mod(193, 47) from dual;

EVAL_SIMPLE_ARITHMETIC_EXPR('193MOD47') MOD(193,47)
--------------------------------------- -----------
                                      5           5

see
http://www.w3.org/TR/xquery/#id-arithmetic
what one can do with this

April 16, 2010 - 10:04 am UTC

very nice!

http://asktom.oracle.com/Misc/evaluating-expression-like-calculator.html

I just wrote that up as "what I learned new today"

Thanks

Anoop, April 19, 2010 - 3:24 am UTC

Hi Tom,
Thanks for your inputs.
I tried using both execute immediate and dynamic sql using dbms_sql.parse.
Execute Immediate did not work on forms6i and dbms_sql.parse worked. So am using dbms_sql.parse for now.
Thanks again for your valuable inputs.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

if on 11 you could

Thanks

More to Explore

DBMS_SQL

SQL to Convert String having mathematical expression into number

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

if on 11 you could

Thanks

More to Explore

DBMS_SQL