Introduction

I ran into a problem at work.  I had a need for SQL Server  to be able to process a dynamic simple math expression string in a SELECT statement.  These are the math operations that were needed: adding, subtracting, multiplying and dividing.

Problem

A customer had a table that contained a string column with simple math expressions.  For example the column could contain something like this:

3.5 * 2.2

3 + 8

5

3 / 4 + 3.2

I needed to execute these expressions and find the result.

Solution

My first idea was to use the EXEC command since SQL Server already knows how to solve these simple math problems.  My issue with using EXEC is that it can’t be used in a SELECT statement or in a function.  It appeared I was going to have to write a custom function that would know how to process certain math operations.  I decided to do a recursive function using charindex to find my predetermined math operators. 

As I started to code the function I ran into a few problems.  First, recursion doesn’t work if you don’t process the operations in order. This makes sense when you think about it, but my first pass at the function didn’t have any logic to figure out which math operator should be processed first.  After I added code to determine which math operator was first, the recursive function worked fine.

The next issue I ran into was charindex returns the first occurrence of the character you are looking for.  When you process the first math operation with a recursive function you are actually processing the math expression from right to left.  Although this worked fine, it gave a different answer to the math expression. 

For example, take this math expression 3 * 4 + 8.  When you process this left to right you do the multiplication first so the answer is 12 + 8 = 20.  When it is processes from right to left you get 3 * 12 = 36.  For western thinkers we think left to right since that in how we read so the first answer makes more logical sense.

I will pause here and acknowledge that I could have set a precedence to process certain operators first.  Or I could have used brackets to control which operations should go together.  I decided I didn’t really want to go there.  Instead I decided I just needed a charindex that would give me the last occurrence of the operator.

I was surprised to not find a lot of great solutions for doing a charindex that provided the last occurrence of a character.  So I wrote my own function.  It is a simple while loop that puts the charindex returned into the start_location optional parameter.

CREATE function [dbo].[fn_reverse_charIndex]
/*********************************************************//* CREATE fn_reverse_charIndex                           *//* ---------------- COMMENTS    ------------------------ *//* Give the last occurrence of the character passed in   *//* ---------------- HISTORY LOG ------------------------ *//*********************************************************/( @char char(1), @expression varchar(255))
returns int
as
begin
  declare @charIdx int, @returnIdx int
  set @returnIdx = 0
    select @charIdx = charindex(@char, @expression)
    while @charIdx <> 0
    begin
        set @returnIdx = @charIdx
        select @charIdx = charindex(@char, @expression,@charIdx+1)
    end
    return @returnIdx
end

Now that I had a custom function that would return the last occurrence of the charindex, I used that in my recursive function.  Now the recursive function properly processes the math expression from left to right as you would expect.

CREATE function [dbo].[fn_simplemath]
( @expression varchar(255))
returns numeric(18,6)
as
begin
declare  @result numeric(18,6), @opr1 varchar(50), @opr2 varchar(50),
@nopr1 numeric(18,6), @nopr2 numeric(18,6),
@multIdx int, @divIdx int, @addIdx int, @subIdx int, @modIdx int,
@firstIdx int
--first get the last index in the expression passed in
select @multIdx = dbo.fn_reverse_charIndex('*',@expression),
       @divIdx = dbo.fn_reverse_charIndex('/',@expression),
       @addIdx = dbo.fn_reverse_charIndex('+',@expression),
       @subIdx = dbo.fn_reverse_charIndex('-',@expression),
       @modIdx = dbo.fn_reverse_charIndex('%',@expression),
       @firstIdx = 1
       
--figure out which operation is last       
if @multIdx > @firstIdx
begin
  set @firstIdx = @multIdx
end
if @divIdx > @firstIdx
begin
  set @firstIdx = @divIdx
end
if @addIdx > @firstIdx
begin
  set @firstIdx = @addIdx
end
if @subIdx > @firstIdx
begin
  set @firstIdx = @subIdx
end
if @modIdx > @firstIdx
begin
  set @firstIdx = @modidx
end
       
--process the last operation and then recursively call this function to process other math operations
if @multIdx = @firstIdx
begin
 
   select @opr1 = SUBSTRING(@expression,0,@multidx)
  select @opr2 = SUBSTRING(@expression,@multidx+1, LEN(@expression) - @multIdx)
 
  select @nopr2 = CAST(@opr2 as numeric(18,6))
  select @nopr1 = dbo.fn_simplemath(@opr1)
  select @result = @nopr1 * @nopr2
 
end
else if @divIdx = @firstIdx
begin
      
   select @opr1 = SUBSTRING(@expression,0,@dividx-1)
  select @opr2 = SUBSTRING(@expression,@dividx+1, LEN(@expression) - @divIdx)
 
  select @nopr2 = CAST(@opr2 as numeric(18,6))
  select @nopr1 = dbo.fn_simplemath(@opr1)
  select @result = @nopr1 / @nopr2
end
else if @addIdx = @firstIdx
begin
      
   select @opr1 = SUBSTRING(@expression,0,@addidx-1)
  select @opr2 = SUBSTRING(@expression,@addidx+1, LEN(@expression) - @addIdx)
 
  select @nopr2 = CAST(@opr2 as numeric(18,6))
  select @nopr1 = dbo.fn_simplemath(@opr1 )
  select @result = @nopr1 + @nopr2
end
else if @subIdx = @firstIdx
begin
      
   select @opr1 = SUBSTRING(@expression,0,@subidx-1)
  select @opr2 = SUBSTRING(@expression,@subidx+1, LEN(@expression) - @subIdx)
 
  select @nopr2 = CAST(@opr2 as numeric(18,6))
  select @nopr1 = dbo.fn_simplemath(@opr1 )
  select @result = @nopr1 - @nopr2
end
else if @modIdx = @firstIdx
begin
      
   select @opr1 = SUBSTRING(@expression,0,@modidx-1)
  select @opr2 = SUBSTRING(@expression,@modidx+1, LEN(@expression) - @modIdx)
 
  select @nopr2 = CAST(@opr2 as numeric(18,6))
  select @nopr1 = dbo.fn_simplemath(@opr1)
  select @result = @nopr1 % @nopr2
end
else begin
    --No operations left so pass back a number
      select @result = CAST(@expression as numeric(18,6))
end
return @result
end

Note: I am sure I will hear some comments about only allowing varchar(255) as the input for the math expression.  Clearly that 255 could be changed to a larger number, or perhaps even just varchar(max).  For my usage varchar(255) worked for me.  Also there are many more math functions that sql server supports.  Clearly they could have been added to this function as well.  Again they were not needed, so I didn’t spend the time to add them.

Conclusion

I was able to write a recursive function that processes simple math expressions and is able to return the answer in a timely fashion in a select statement.   I also wrote a reverse charindex function so that my recursive function would process the simple math expressions from left to right as would be expected.  I hope you enjoy these functions and find them useful in whatever problems you are facing.