A simple math expression solver

• 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.

• 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.

• ------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

• 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

• 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

• This space intentionally left blank

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

• 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

• 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.

• 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

• I like this thread because it furnishes a nice object lesson in why you don't go reinventing wheels.

• 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.

• 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.

• 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.

• 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 42 total)