Eval arithmetic statement function in SQL

,

You can use it to evaluate statement like '3+4/2-1' in sql server.

You can call this function like select dbo.EvalSQLStatement('4+5*6-3') and this will return 31 in this example.

create function EvalSQLStatement(@strStatement as varchar(1000))
returns decimal(12,2)
as
begin

Declare @expr as varchar(1000);
Declare @expr1 as varchar(20);
Declare @expr2 as varchar(20);
Declare @expr3 as varchar(50);
Declare @var1 as decimal(12,2);
Declare @operator as varchar(2);
Declare @position as integer;
Declare @NrofChar as integer;
Declare @positionOriginal as integer;

set @expr =@strStatement

if CHARINDEX('/',@expr) > 0
set @operator = '/'
else if CHARINDEX('*',@expr) > 0
set @operator = '*'
else if CHARINDEX('-',@expr) > 0
set @operator = '-'
else if CHARINDEX('+',@expr) > 0
set @operator = '+'
else
set @operator = 'no'

set @NrofChar = 0
set @positionOriginal = CHARINDEX(@operator,@expr)
--select @position
set @expr1=''
set @position = @positionOriginal
--select substring(@expr,@position,1)
while (ASCII(substring(@expr,@position+1,1))>47 OR ASCII(substring(@expr,@position+1,1))=46)
begin
    set @expr1 = @expr1 + substring(@expr,@position+1,1)
    set @position = @position + 1
    set @NrofChar = @NrofChar + 1
end
set @position = @positionOriginal-1
set @expr2=''

while ASCII(substring(@expr,@position,1))>47 OR substring(@expr,@position,1) = '.'
begin
    set @expr2 = substring(@expr,@position,1)+@expr2 
    set @position = @position - 1
    set @NrofChar = @NrofChar + 1
end

if @operator = '/'
    set @expr3 = cast(cast((cast(@expr2 as decimal(9,2)) / cast(@expr1 as decimal(9,2))) as decimal(10,2)) as varchar(50))
else if @operator = '*'
    set @expr3 = cast((cast(@expr2 as decimal(9,2)) * cast(@expr1 as decimal(9,2))) as varchar(50))
else if @operator = '-'
    set @expr3 = cast((cast(@expr2 as decimal(9,2)) - cast(@expr1 as decimal(9,2))) as varchar(50))
else if @operator = '+'
    set @expr3 = cast((cast(@expr2 as decimal(9,2)) + cast(@expr1 as decimal(9,2))) as varchar(50))

if @operator ='no'
begin 
    set @var1 = cast(@expr as decimal(9,2));
    return @var1
end
else
begin
    set @expr = stuff(@expr,case @position when 0 then 1 else @position+1 end ,@NrofChar+1,@expr3)
    set @expr=dbo.EvalSQLStatement(@expr)
end
set @var1 = cast(@expr as decimal(9,2));
return @var1;
end

Rate

2.11 (9)

Share

Share

Rate

2.11 (9)