Home » SQL & PL/SQL » SQL & PL/SQL » Evaluate a dynamic math expression in SQL (Oracle 10G database)
Evaluate a dynamic math expression in SQL Sun, 22 June 2008 07:50
 satya.vanukuri Messages: 2Registered: June 2008 Junior Member
I have a user interface where the user can enter a formula using a set of parameters and operators. For eg , the user is given a list of parameters, say : Width, Height , Gauge. The list of operators include standard math functions : + - / * ^

He can select and create a formula like gauge * width. This is stored in a table. At a later time a job is scheduled to evaluate the formula based on parameters for width, gauge and height.

I can evaluate the expression by substituting each parameter.
I can do this using dynamic sql. However I'm not sure how to evaluate the ^ symbol. The equivalent of this is power function.

So if a user enters (height * 2) + (width^2) . How do i evaluate this. I am doing this in pl/sql.

thanks
satya
Re: Evaluate a dynamic math expression in SQL [message #328727 is a reply to message #328726] Sun, 22 June 2008 07:55
 Michel Cadot Messages: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
POWER

Regards
Michel
Re: Evaluate a dynamic math expression in SQL [message #328729 is a reply to message #328727] Sun, 22 June 2008 08:07
 satya.vanukuri Messages: 2Registered: June 2008 Junior Member
I am aware it is the power function. My question is if hte user enters a formula like

((0.25*width)+((gauge+height)^0.52)), I need to convert this to

((0.25*width)+(power(gauge+height),0.52)) since the symbol ^ doesn't mean anything in sql. how do i do this.

And ofcourse the ^ can repea any number of times. How do i do this.

thanks,

[Updated on: Sun, 22 June 2008 08:08]

Report message to a moderator

Re: Evaluate a dynamic math expression in SQL [message #328730 is a reply to message #328729] Sun, 22 June 2008 08:17
 Michel Cadot Messages: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Analyze the string and convert it.
what do you expect?
Of course, it is easier and more efficient if this is the inferface that does it.

Regards
Michel
Re: Evaluate a dynamic math expression in SQL [message #472306 is a reply to message #328730] Thu, 19 August 2010 16:04
 selfnick Messages: 1Registered: August 2010 Junior Member
I've got it.

You can execute an SQL like this, isn't it?

SQL> select 4*5 from dual;
20

Let's call '4*5' "the expression".

And you can execute dynamic SQL cursors, right?

The solution it's create a function with a varchar parameter called expression and a varchar return.

In that function, you will create a dynamic cursor like this:

'select ' || expression || ' from dual'

SQL> select my_eval_function('5*2') from dual;
10

Easy, isn't it?

Best wishes,

Mario Fernando S.
Colombia
Re: Evaluate a dynamic math expression in SQL [message #472313 is a reply to message #472306] Thu, 19 August 2010 16:51
 cookiemonster Messages: 12820Registered: September 2008 Location: Rainy Manchester Senior Member
While the help is appreciated can you:
1) Read the orafaq forum guide
2) Read the question more carefully - the OP knows how to do what you describe, what he's asking is how to substitute the power function for ^
Re: Evaluate a dynamic math expression in SQL [message #472386 is a reply to message #328729] Fri, 20 August 2010 05:28
 _jum Messages: 515Registered: February 2008 Senior Member
regexp could help, as simple example use:
```WITH data AS (SELECT '3.4+(gauge+height)^0.52-17.6' expr FROM dual)
SELECT expr,
regexp_replace(expr,'\((.+)\)\^([0-9\.]*)(.*)','power(\1,\2)\3') nexpr
FROM data;

3.4+(gauge+height)^0.52-17.6
3.4+power(gauge+height,0.52)-17.6
```
 Previous Topic: Trim Out Special Character... Next Topic: Trigger for calling same procedure for different column updation in a tabel
Goto Forum:

Current Time: Sat Aug 19 02:34:32 CDT 2017

Total time taken to generate the page: 0.05580 seconds