Skip to Main Content
  • Questions
  • SQL to Convert String having mathematical expression into number

Breadcrumb

Question and Answer

Tom Kyte

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 1000+ times

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)

Is this answer out of date? If it is, please let us know via 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
Tom Kyte
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.

More to Explore

DBMS_SQL

More on PL/SQL routine DBMS_SQL here