-- select dbo.fnSimpleMath( '3.5 * 2.2' ) , dbo.fnSimpleMath( '3 + 8' ) , dbo.fnSimpleMath( '5' ), dbo.fnSimpleMath( '3 / 4 + 3.2 ' ) , dbo.fnSimpleMath( '1/0' )alter function dbo.fnSimpleMath(@string varchar(max))returns numeric(18,6)as begin declare @math as table ( [sign] varchar(1), [orderby] int, addby numeric(18,6), subby numeric(18,6), multby numeric(18,6), divby numeric(18,6))insert into @math values ('+',2,1.0,0,0,0),('-',2,0,1.0,0,0),('*',1,0,0,1.0,0),('/',1,0,0,0,1.0)declare @StringSep varchar(max) = replace(replace(replace(replace(@String, '+','|'), '-','|'), '/','|'), '*','|')+ '|'declare @result numeric(18,6) = 0select @result = ((@result + data )* isnull(math.addby,1.0) ) + ((@result - data )* isnull(math.subby,0) ) + (@result * data * isnull(math.multby ,0) ) + isnull((@result / nullif(data,0) * isnull(math.divby ,0) ),0)from (select convert(numeric(18,6), SUBSTRING(@String, Numbers.N+1 ,CHARINDEX('|', @StringSep , Numbers.N+1) - Numbers.N-1)) as data ,SUBSTRING(@String, Numbers.N, 1) as symbol FROM dbo.Tally Numbers WITH (NOLOCK) WHERE Numbers.N <= LEN(@String) AND SUBSTRING('|'+@StringSep, Numbers.N+1, 1)='|' ) T left outer join @math math on math.sign = symbolreturn @resultend

declare @string varchar(max) = '3.5 * 2.2'select convert(numeric(18,6),CONVERT(varchar(max), CONVERT(xml,'').query(@string)))

--figure out which operation is last if @addIdx > @firstIdxbegin set @firstIdx = @addIdx set @precedent = 1endif @subIdx > @firstIdx begin set @firstIdx = @subIdx set @precedent = 1end--Add restriction that addition/subtraction must be found first (and so done last)if @multIdx > @firstIdx AND @precedent = 0begin set @firstIdx = @multIdxendif @divIdx > @firstIdx AND @precedent = 0begin set @firstIdx = @divIdxendif @modIdx > @firstIdx AND @precedent = 0begin set @firstIdx = @modidxend

select @nopr2 = dbo.fn_simplemath(@opr2) select @nopr1 = dbo.fn_simplemath(@opr1)

CREATE FUNCTION [dbo].[fn_SimpleMathEval] ( @expression VARCHAR(255) )RETURNS NUMERIC(18, 6)AS BEGIN DECLARE @result NUMERIC(18, 6) IF LEN(ISNULL(@expression, '')) = 0 SET @result = NULL ELSE BEGIN SET @result = CASE WHEN CHARINDEX('+', @expression) <> 0 THEN dbo.fn_SimpleMathEval(LTRIM(RTRIM(LEFT(@expression, CHARINDEX('+', @expression) - 1)))) + dbo.fn_SimpleMathEval(LTRIM(RTRIM(RIGHT(@expression, LEN(@expression) - CHARINDEX('+', @expression))))) WHEN CHARINDEX('-', @expression) <> 0 THEN dbo.fn_SimpleMathEval(LTRIM(RTRIM(LEFT(@expression, CHARINDEX('-', @expression) - 1)))) - dbo.fn_SimpleMathEval(LTRIM(RTRIM(RIGHT(@expression, LEN(@expression) - CHARINDEX('-', @expression))))) WHEN CHARINDEX('*', @expression) <> 0 THEN dbo.fn_SimpleMathEval(LTRIM(RTRIM(LEFT(@expression, CHARINDEX('*', @expression) - 1)))) * dbo.fn_SimpleMathEval(LTRIM(RTRIM(RIGHT(@expression, LEN(@expression) - CHARINDEX('*', @expression))))) WHEN CHARINDEX('/', @expression) <> 0 THEN dbo.fn_SimpleMathEval(LTRIM(RTRIM(LEFT(@expression, CHARINDEX('/', @expression) - 1)))) / dbo.fn_SimpleMathEval(LTRIM(RTRIM(RIGHT(@expression, LEN(@expression) - CHARINDEX('/', @expression))))) WHEN ISNUMERIC(@expression) = 1 THEN CONVERT(NUMERIC(18, 6), @expression) ELSE NULL END END RETURN @result END