November 20, 2011 at 8:43 pm
Comments posted to this topic are about the item A simple math expression solver
November 21, 2011 at 6:31 am
Interesting stuff.
Performance-wise, how does fn_reverse_charIndex hold up against (LEN(@expr) - CHARINDEX(@char, REVERSE(@expr)) + 1)
And, seeing as your fn_simplemath function appears only to handle one expression at a time, how would it (injection aside) hold up against:
CREATE TABLE #result (res NUMERIC);
INSERT INTO #result (res) EXEC ('SELECT ' + @expr)
SELECT res FROM #result
DROP TABLE #result
J.
November 21, 2011 at 8:50 am
Pretty cool!
I've been interested in writing a tokenizer and recursive descent parser for handling user entered expressions with variables (column names). But, I knew it would take a week or more to get it to be extensible. Anyone up for that challenge?
November 21, 2011 at 9:37 am
Interesting code.
But can't use just do either of these:
DECLARE @result TABLE (value decimal(24, 4))
INSERT INTO @result
EXEC('SELECT 3.0 / 4 + 3.2')
SELECT * FROM @result
Or:
DECLARE @value decimal(24, 4)
EXEC sp_executesql N'SELECT @value = 3.0 / 4 + 3.2', N'@value decimal(24, 4) OUTPUT', @value OUTPUT
PRINT @value
?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 21, 2011 at 9:53 am
In my article I state that I considered using some form of exec. The issue is I needed this to be done in a function. Functions cannot use exec.
Thanks,
Ben
November 21, 2011 at 10:39 am
But function can use xml data type... 😉
declare @x xml=''
select @x.query('10+2*3.2') as result
November 21, 2011 at 10:56 am
Why not just sqlclr + any of the brazillion c# expression parsers?
https://www.google.com/#sclient=psy-ab&hl=en&source=hp&q=c%23+math+expression+parser&pbx=1&oq=c%23+math+express&aq=0&aqi=g2g-C1g-jC1&aql=&gs_sm=e&gs_upl=0l0l1l79l0l0l0l0l0l0l0l0ll0l0&bav=on.2,or.r_gc.r_pw.r_cp.,cf.osb&fp=ea0e067728996111&biw=1231&bih=652
More generally, why chew up db cycles (affecting all db users) with this at all, rather than put the dotnet code in the client app itself (affecting only the user who needs it)?
November 21, 2011 at 10:59 am
When I try to put your code into a function passing in a string variable I get an error:
The argument 1 of the xml data type method "query" must be a string literal.
That seems to suggest it won't work in a function since it has to be a static string not a dynamic variable.
I like the idea though. Thanks for the post.
Ben
November 21, 2011 at 11:02 am
I don't have anything against the sql clr. In this case since I just needed a simple math expression solver, it seemed a bit over kill to call the clr to solve a simple math expression. My guess would be that unless the math expression is quite long this function will probably out perform the clr and the CPU impact would be minimal. If I had the requirement to handle more complex math expressions then the clr with .net code would be a clear solution. Thanks for your post.
Ben
November 21, 2011 at 11:15 am
ScottPletcher(11/21/2011)
Interesting code.
But can't use just do either of these:
Looks like you and I had the same idea...
To answer my own question, I found that integer division was a bit of a pain when run through an INSERT/EXEC, even when storing in a DECIMAL field. You have to either format the numbers with decimal points (as you have done), or cast each one in turn.
Of course, that is an known issue/design-choice with SQL Server's handling of integers, but I imagine a well-functioning expression parser would overcome this by explicitly casting the numbers to a non-integral numeric type.
The OP's code appears to cast each operand to NUMERIC(18, 6), which would overcome the issue, at least to that precision.
November 21, 2011 at 11:24 am
You earler post had a commet about the function only handles one expression at a time. That was kind of the point. In a select statement the function could be called for multiple expressions. That is actually the case in my situation. A table column contains a simple math expression. So using a function would allow me to get the answer to as many lines as would get returned in the select statement.
Thanks for your posts.
Ben
November 21, 2011 at 12:17 pm
The method for evaluating algebraic equations is properly done through the use of reverse polish notation or more formally known as postfix notation. (It's referred to a reverse Polish notation because a Polish fellow figured it out) I've written these programs in COBOL, never thought about doing it in in T-SQL, maybe VB.NET. Could be a challenge. Through the use of cursors it might not be that difficult. I'll see if I can come up with something in a few days. You can use this method with a few modifications to evaluate formal logic as well. i.e is (A > B) or (C > B & D = B) a true statement. That can get very complex, trust me. So can algebra.
November 21, 2011 at 12:26 pm
My earlier post was used in a software package to evaluare formulas in a table. The table was comprised of three "columns". The first column had the user friendly version of the forumla, the second column had the postfix form of that equation, the third was a key field used by an evaluation program to find the proper formula. The the evaluater program would read the table based on the key column and evaluate the second column streight away. Prettly slick, really.
When I say user friendly think of (a + b) / c. I don't have access to my programs right now to show the exact form of that in postfix but t is something like: c!/!a!b!+ (the ! is used as a separator similar to a , in a .csv file). To get the user friendly formula into postfix you have to use stack processing which lends itself to cursor processing (I think).
November 21, 2011 at 12:27 pm
I would stay away from cursors if you decide to do it in t-sql. It might be better to use vb.net or c# and then use the clr. Although I believe you could still use a recursive function call if you were able to properly parse the equation.
Ben
November 21, 2011 at 12:33 pm
The key to this method is the formulas have already been converted to postfix notation and all the evaluator function has to do is peddle down the string evaluating the variables as you go and then applying the op code (+ - * /,etc.) to the variable. If you use an HP12c calculator you would be familiar with postfix notation. Formulas are generally pre-thought out and entered via key strokes. Once the operator exits that field the program would immediately format the formula into postfix noation ready for the processing program to select it and evaluate it. You have to think how something like this would be used. At a data entry level you don't have to worry about speed.
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply