Evaluate a dynamic math expression in SQL [message #328726] |
Sun, 22 June 2008 07:50 |
satya.vanukuri
Messages: 2 Registered: 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.
Any ideas, please help.
thanks
satya
|
|
|
|
|
|
Re: Evaluate a dynamic math expression in SQL [message #472306 is a reply to message #328730] |
Thu, 19 August 2010 16:04 |
selfnick
Messages: 1 Registered: 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 #472386 is a reply to message #328729] |
Fri, 20 August 2010 05:28 |
_jum
Messages: 577 Registered: 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
|
|
|