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