[转]根据表达式计算数值--如select dbo.evalfunction('30*(2+3)/3')

/*转换表达式*/
create Function [dbo].[EvalFunction] (@expression varchar(1000))
 returns Money
as
begin 
/*
declare @expression varchar(1000) set @expression ='(100+200+0.5)*0.211' */
declare @operatorList table (id [int] IDENTITY(1,1) NOT NULL,exp1 varchar(100))
declare @Operator1 varchar ,@operator3 varchar(100)
declare @expressionString varchar(1000)
declare @numbers varchar(101)
declare @reuslt money
set @numbers='1234567890.'
set @expressionString=''
set @expression=replace(@expression,' ','')
while len(@expression)>0
begin
 set @operator3='';
 /*取出数字处理*/
 if charindex(substring(@expression,1,1),@numbers)>0
 begin 
  while charindex(substring(@expression,1,1),@numbers)>0   /*如果是一个数字*/
  begin 
   set  @operator3 =@operator3+ substring(@expression,1,1) 
   set @expression = Substring(@expression,2,LEN(@expression)-1) 
   if @expression=''
   break
  end
  set @expressionString   =@expressionString+@operator3 + '|';
 end
 /*取(处理*/
 if(len(@expression)>0 and SUBSTRING(@expression,1,1)='(')
 begin
  insert into @operatorList (exp1) values('(');
  set @expression = Substring(@expression,2,LEN(@expression)-1)
 end
 /*取)处理*/
 set @operator3='';
 if(len(@expression) >0 and SUBSTRING(@expression,1,1)=')')
    begin
  while 2>1
  begin
   if  (select top 1 exp1 from  @operatorList order By id desc)!='('
   begin
     set  @operator3 =@operator3+ (select top 1 exp1 from  @operatorList order By id desc) +'|'
    delete from @operatorList where id =(select top 1 id from  @operatorList order By id desc)
   end
   else
   begin
    delete from @operatorList where id =(select top 1 id from  @operatorList order By id desc)
    break
   end
  end
  set @expressionString  =@expressionString+@operator3
  set @expression = Substring(@expression,2,LEN(@expression)-1)
    end
 
  /*   处理运算符 */
  set @operator3 = '';
   if LEN(@expression)>0 and  (SUBSTRING(@expression,1,1)='+'  or SUBSTRING(@expression,1,1)='-'  or SUBSTRING(@expression,1,1)='*'  or SUBSTRING(@expression,1,1)='/' or SUBSTRING(@expression,1,1)='%')
 begin
 set  @Operator1=SUBSTRING(@expression,1,1); 
 declare @t int;
 set @t= (select count(*) from @operatorList)
  if(select count(*) from @operatorList) > 0
   begin
     if (select top 1 exp1 from  @operatorList order By id desc)='('
     or (
     (@Operator1='*' and (select top 1 exp1 from  @operatorList order By id desc)='+')
     or
     (@Operator1='*' and (select top 1 exp1 from  @operatorList order By id desc)='-')
     or
     (@Operator1='/' and (select top 1 exp1 from  @operatorList order By id desc)='+')
     or
     (@Operator1='/' and (select top 1 exp1 from  @operatorList order By id desc)='-')
     or
     (@Operator1='%' and (select top 1 exp1 from  @operatorList order By id desc)='+')
     or
     (@Operator1='%' and (select top 1 exp1 from  @operatorList order By id desc)='-'))
    
     insert into @operatorList (exp1) values(@Operator1);
     else
     begin 
      set  @operator3  =@operator3+ (select top 1 exp1 from  @operatorList order By id desc) +'|'
         delete from @operatorList where id =(select top 1 id from  @operatorList order By id desc)
      insert into @operatorList (exp1) values(@Operator1);
      set @expressionString =@expressionString+@operator3  
                 end
   end 
   else
    begin 
     insert into @operatorList (exp1) values(@Operator1);
    end 
  
  set @expression = Substring(@expression,2,LEN(@expression)-1)
 end 
 
end 
set @operator3 = '';
     while  (select count(*) from @operatorList) > 0
       begin
         set  @operator3  =@operator3+ (select top 1 exp1 from  @operatorList order By id desc) +'|'
         delete from @operatorList where id =(select top 1 id from  @operatorList order By id desc)
      end
 set @expressionString  =@expressionString+Substring(@operator3,1,LEN(@operator3)-1)  + '|'  
/*计算顺序表达式*/
 declare @calculateList table (id [int] IDENTITY(1,1) NOT NULL,exp1 varchar(100))
 declare @o1 money ,@o2 money
 declare @o3 table (id [int] IDENTITY(1,1) NOT NULL,exp1 varchar(100))
/*模拟Split操作*/
declare @i int
set @expressionString=rtrim(ltrim(@expressionString))
set @i=charindex('|',@expressionString)
while @i>=1
begin
 
 insert @o3 values(left(@expressionString,@i-1))
 set @expressionString=substring(@expressionString,@i+1,len(@expressionString)-@i)
 set @i=charindex('|',@expressionString) 
end 
/*清理临时表达到ForEach效果*/
DECLARE @tempId INT
Declare @tempValue varchar(100)
Declare @calculateResult money
WHILE EXISTS ( SELECT [id] FROM @o3 )
BEGIN
 Select Top 1 @tempId=id,@tempValue=exp1 FROM @o3 
 if(@tempValue='-' or @tempValue='+' or @tempValue='*' or @tempValue='/' or @tempValue='%'  )
 begin
  /*两个操作数退栈和一个操作符退栈计算*/
  select top 1 @o2=convert(money,exp1) from @calculateList order by id desc
  delete @calculateList where id=(select Top 1 id from @calculateList order by id desc)
  select top 1 @o1=convert(money,exp1) from @calculateList order by id desc
  delete @calculateList where id=(select Top 1 id from @calculateList order by id desc)
   /*局部计算完毕从新插入数据*/
   if(@tempValue='+')
  set @calculateResult=convert(money,@o1)+CONVERT(money,@o2)
   if(@tempValue='-')
  set @calculateResult=convert(money,@o1)-CONVERT(money,@o2)
   if(@tempValue='*')
  set @calculateResult=convert(money,@o1)*CONVERT(money,@o2)
   if(@tempValue='/')
  set @calculateResult=convert(money,@o1)/CONVERT(money,@o2)
   if(@tempValue='%')
  set @calculateResult=convert(money,@o1)%CONVERT(money,@o2)
  
  insert into  @calculateList (exp1) values(@calculateResult)  
 end 
 else
 begin
  insert into  @calculateList (exp1) values(@tempValue)
 end
 
 DELETE FROM @o3 WHERE [id] = @tempId 
END 
 
 
  return  @calculateResult
 end