November 21, 2011 at 1:28 pm
I wonder if it would be fair to call this code a simplified RPN processor. Essentially you are taking infix notation and putting into a form that used to be required on some calculators.
November 21, 2011 at 1:33 pm
Sort of. The code for evaluating a simple formula like mentioned early in this thread could be called a simplified RPN evaluator, I guess. I really didn't spend much time on it because I know intemately what an RPN process is all about. A real RPN processor can evaluate any depth algebraic equation.
November 21, 2011 at 1:53 pm
------considering execution for many rows-----------------
DECLARE @ExprCollection Table(expr VARCHAR(100) )
INSERT INTO @ExprCollection(expr)
SELECT '2+1'
UNION ALL
SELECT '5*2'
UNION ALL
SELECT '6/3'
DECLARE @genSql AS VARCHAR(MAX)
CREATE TABLE #ExpValueTable ([value] INT,[Id_Exp] [VARCHAR](100) PRIMARY KEY)
SELECT @genSql = CASE WHEN @genSql Is Null
THEN ' INSERT INTO #ExpValueTable ([value],[Id_Exp]) ' + ' SELECT ' + expr + ' AS ExpValue,''' + expr +''' As Exp'
ELSE @genSql + ' UNION SELECT ' + expr + ' AS ExpValue,''' + expr +''' As Exp' END
FROM @ExprCollection
GROUP BY expr
EXEC(@genSql)
SELECT *
FROM #ExpValueTable
--- OR JOIN YOUR TABLE TO THE TEMPORARY TABLE... AND BETTER USING THE TABLE PRIMARY KEY
DROP TABLE #ExpValueTable
November 21, 2011 at 2:20 pm
In this example the size of the table of literals is unlimited. However aditional coding is needed tu use variable names i.e. A, B, C, etc. A little fooling around with this thing and it might serve the most elementary uses.
DECLARE @ExprCollection Table(expr VARCHAR(100) )
INSERT INTO @ExprCollection(expr)
SELECT '2+1'
UNION ALL
SELECT '5*2'
UNION ALL
SELECT '6/3'
drop table #ExpValueTable
CREATE TABLE #ExpValueTable ([value] decimal(18,4),[Id_Exp] [VARCHAR](100) PRIMARY KEY)
declare ExprCursor cursor
for
select * from @ExprCollection
declare @Expr as varchar(100), @SQL varchar(max)
open ExprCursor
fetch next from ExprCursor into @Expr
while @@Fetch_Status = 0
BEGIN
set @SQL = ' INSERT INTO #ExpValueTable ([value],[Id_Exp]) SELECT ' + @expr + ' AS ExpValue,''' + @expr +''' As Exp'
fetch next from ExprCursor into @Expr
exec (@SQL)
END
close ExprCursor
deallocate ExprCursor
select * from #ExpValueTable
November 21, 2011 at 4:46 pm
When I ran Ben's code, I had some problems if I didn't leave spaces between the operators and the values. i.e., fn_simplemath('3 - 4') worked while fn_simplemath('3-4') failed... I suspect this would require a minor tweaking of some of the substring arguments. But trying to debug a recursive function can be a bit troublesome.
It also occurred to me that since were were not concerned with operator precedence and simply calculating from left to right, we should be able to simply loop through the string without needing to be recursive.
My approach was to create a string that duplicates the input except for replacing all the "acceptable" operator values with a single unique character, in my case I used a tilde(~). This second string can then be used to identify the position of all the operators in the original string and make it easier to parse out the individual operand values
Here is my code:
CREATE Function [dbo].[fn_simplemath2]
( @Expression1 varchar(255))
returns numeric(18,6)
As
BEGIN
--
-- @Expression2 will duplicate @Expression1 with all operators replaced with ~
Declare @Expression2 varchar(255)
Set @Expression2 =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Expression1,'-','~'),'+','~'),'*','~'),'/','~'),'%','~')
--
-- Local variables
Declare @PrevOpLoc int -- Location of previous operator
Declare @NextOpLoc int -- Location of next operator
Declare @OpChar Char(1) -- Current operator character
Declare @Result numeric(18,6) -- Hold running calculation and final return value
Declare @NextVal numeric(18,6) -- the next substring value to be used to modify result base on operator
--
-- Find the first operator
Set @NextOpLoc = CHARINDEX('~',@Expression2)
--
-- Initialize @Result to the first substring, If there are no operators, move entire string to @Result
Set @Result =
CASE
When @NextOpLoc = 0 then CAST(@Expression1 as numeric(18,6))
Else CAST(LEFT(@Expression1,@NextOpLoc-1) as numeric(18,6))
END
--
-- Now we will loop until we run out of operators
While @NextOpLoc <> 0
BEGIN
-- Get the actual operator from @Expression1, pull out the next substring value
Set @OpChar = SUBSTRING(@Expression1,@NextOpLoc,1)
Set @PrevOpLoc = @NextOpLoc
Set @NextOpLoc = CHARINDEX('~',@Expression2, @NextOpLoc + 1)
Set @NextVal= Cast(
SUBSTRING(@Expression1,@PrevOpLoc+1,
Case
When @NextOpLoc = 0 then LEN(@Expression1)
Else @NextOpLoc-1
End
- @PrevOpLoc) as numeric(18,6))
--
-- Perform the appropriate operation
Set @Result =
Case @OpChar
When '-' then @Result - @NextVal
When '+' then @Result + @NextVal
When '*' then @Result * @NextVal
When '/' then @Result / @NextVal
When '%' then @Result % @NextVal
Else null
End
END
Return @Result
END
November 21, 2011 at 4:49 pm
This space intentionally left blank
November 21, 2011 at 4:59 pm
Attempting to reply to Bill Talada...
Bill, I'm not entirely sure I'm understanding your request...
To paraphrase Dr. McCoy, I'm just an old country programmer , so I'm not following some of your terms ( such as "tokenizer") I have written parsing routines using T-SQl table functions which can take column values containing comma (or other delimiter) separated strings and returns a table of the parsed values. If this seems close to what you are looking for, let me know
November 21, 2011 at 5:54 pm
Can some one improve my solution:
------considering execution for many rows-----------------
DECLARE @ExprCollection Table(expr VARCHAR(100) )
INSERT INTO @ExprCollection(expr)
SELECT '2+1'
UNION ALL
SELECT '5*2'
UNION ALL
SELECT '6/3'
DECLARE @genSql AS VARCHAR(MAX)
CREATE TABLE #ExpValueTable ([value] INT,[Id_Exp] [VARCHAR](100) PRIMARY KEY)
SELECT @genSql = CASE WHEN @genSql Is Null
THEN ' INSERT INTO #ExpValueTable ([value],[Id_Exp]) ' + ' SELECT ' + expr + ' AS ExpValue,''' + expr +''' As Exp'
ELSE @genSql + ' UNION SELECT ' + expr + ' AS ExpValue,''' + expr +''' As Exp' END
FROM @ExprCollection
GROUP BY expr
EXEC(@genSql)
SELECT *
FROM #ExpValueTable
--- OR JOIN YOUR TABLE TO THE TEMPORARY TABLE... AND BETTER USING THE TABLE PRIMARY KEY
DROP TABLE #ExpValueTable
November 21, 2011 at 6:40 pm
I don't think this is it works right -
1. select dbo.fn_simplemath('10 + 12 * 2') - this gives me result as 44.000000 which is wrong.
2. select 10 + 12 * 2 - this gives me 34 - this is correct.
I think author missed implementing the BODMAS rule.
November 21, 2011 at 7:22 pm
The article talks about Left To Right processing vs Right To Left processing, and creates a reverse charindex function in order to swap the 'normal' operation of charindex.
However, as the prior post pointed out, mathematical expressions are not evaluated correctly by either Left to Right or Right To Left fashion.
They should be evaluated by the rules of precedence, namely:
Highest: ( )
Medium: * /
Lowest: + -
(I left off numerous operators, but these should suffice for a simple math expression solver.)
A RPN function should take these rules into consideration (in terms of when to pop and when to push operators).
As a humorous aside on Reverse Polish Sausage:
-wbw
November 21, 2011 at 9:09 pm
I like this thread because it furnishes a nice object lesson in why you don't go reinventing wheels.
November 21, 2011 at 9:26 pm
You know, I find your comment offensive. I put time and effort into this article and this code. I had a problem to solve that required a function to solve simple math problems. When I researched for an existing function that did this, I didn't find one. So your comment on why not to reinvent the wheel in not based in reality.
Also, I have never presented this function to be an "be all end all function" for everything. The title is Simple math expression solver for a reason. I choose not to get complicated and to just process the expression from left to right. I believe I was quite clear in my article on this.
I think before some of you posters get too critical of someones work, you should perhaps try to write something orginal yourself. Then try to write a decent article about it.
November 22, 2011 at 1:01 am
bkubicek (11/21/2011)
You know, I find your comment offensive. I put time and effort into this article and this code. I had a problem to solve that required a function to solve simple math problems. When I researched for an existing function that did this, I didn't find one. So your comment on why not to reinvent the wheel in not based in reality.Also, I have never presented this function to be an "be all end all function" for everything. The title is Simple math expression solver for a reason. I choose not to get complicated and to just process the expression from left to right. I believe I was quite clear in my article on this.
I think before some of you posters get too critical of someones work, you should perhaps try to write something orginal yourself. Then try to write a decent article about it.
Definitely not clear enough I was surprised by the suggestion that a solution would make more sense for us from west and another solution for others, surely for your client/firm the expression has only one solution, if it was notation computation that should've been mentioned in the article for example.
There have been a few people posting their own versions in the thread for both reverse polish notation and normal solutions.
My first idea was to use the EXEC command since SQL Server already knows how to solve these simple math problems.
By your suggestion in the article quoted above I'd say the solution in the article is wrong since the code and exec with select will disagree on either 2 * 2 + 7 or 2 + 2 * 7.
November 22, 2011 at 1:02 am
Unfortunately (or fortunately) the last occurence problem can be better addressed by using charindex with the built-in reverse function. More importantly there is absolutely no reason to use such a function given the problem area.
You begin by noting that left to right doesn't always work, and so you opt for right to left. When in fact, one is no better than the other. It is of course the precedence of the operators used that must determine the order. To finish up with a function that will often produce an incorrect result is not a good thing.
Perhaps the conclusion should simply have been that the idea was flawed to begin with. It happens.
Hope you take the criticism constructively.
David McKinney.
November 22, 2011 at 6:58 am
Just copy to Manag Stu and run....
Can some one improve my solution:
------considering execution for many rows-----------------
DECLARE @ExprCollection Table(expr VARCHAR(100) )
INSERT INTO @ExprCollection(expr)
SELECT '2+1'
UNION ALL
SELECT '5*2'
UNION ALL
SELECT '6/3'
DECLARE @genSql AS VARCHAR(MAX)
CREATE TABLE #ExpValueTable ([value] INT,[Id_Exp] [VARCHAR](100) PRIMARY KEY)
SELECT @genSql = CASE WHEN @genSql Is Null
THEN ' INSERT INTO #ExpValueTable ([value],[Id_Exp]) ' + ' SELECT ' + expr + ' AS ExpValue,''' + expr +''' As Exp'
ELSE @genSql + ' UNION SELECT ' + expr + ' AS ExpValue,''' + expr +''' As Exp' END
FROM @ExprCollection
GROUP BY expr
EXEC(@genSql)
SELECT *
FROM #ExpValueTable
--- OR JOIN YOUR TABLE TO THE TEMPORARY TABLE... AND BETTER USING THE TABLE PRIMARY KEY INSTEAD OF Id_Exp
DROP TABLE #ExpValueTable
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply