SQL Server中计算表达式的和

项目使用的是SQL Server数据库，需要做一个审核规则，字段A中表达式的值和字段B中的值，做比较：

 

需求本身很简单，但是表达式中存在很多非法字符（非法全角，运算符，汉字……）

eg：1.1.1*2；1*+2，……

因此需要判断，否则直接运算，会报异常

具体SQL如下：

ALTER FUNCTION [dbo].[getNumByExpressions]( @Number nvarchar(500))
RETURNS numeric(10,4)
--返回-1，表示字符串存在问题
--else返回其计算结果
AS
begin
declare @retrunNum numeric(10,4)
declare @NumberStar nvarchar(500)
declare @a numeric(10,4)
declare @b numeric(10,4)

declare @a1 nvarchar(500)
declare @b1 nvarchar(500)

declare @Numberillegal nvarchar(500)
declare @Numberdouble nvarchar(500)

set @retrunNum=0
--判断非法字符
if PATINDEX('%[^0123456789+*.]%', @Number)>0
begin
return -1
end
--判断2个运算符相连接
else if CHARINDEX('..',@Number)>0 or CHARINDEX('**',@Number)>0 or CHARINDEX('++',@Number)>0 or
CHARINDEX('*.',@Number)>0 or CHARINDEX('.*',@Number)>0 or CHARINDEX('+.',@Number)>0 or
CHARINDEX('.+',@Number)>0 or CHARINDEX('*+',@Number)>0 or CHARINDEX('+*',@Number)>0 or rtrim(ltrim(@Number))=''
begin
return -1
end
--判断运算是否在开头和结尾
else if left(@Number,1)='.' or left(@Number,1)='*' or left(@Number,1)='+' or
right(@Number,1)='.' or right(@Number,1)='*' or right(@Number,1)='+'
begin
return -1
end
else
begin
--数字全角判断
set @Numberdouble=@Number
while isnull(len(@Numberdouble),0)>0
begin
if ascii(left(@Numberdouble,1))=163
begin
return -1
end
if len(@Numberdouble)>1
begin
set @Numberdouble = right(@Numberdouble,len(@Numberdouble)-1)
end
else
begin
set @Numberdouble=null
end

end
--计算结果
while CHARINDEX('+',@Number)>0
begin
set @NumberStar = SUBSTRING(@Number,0,CHARINDEX('+',@Number))
set @Number=SUBSTRING(@Number,CHARINDEX('+',@Number)+1,len(@Number)-CHARINDEX('+',@Number))
if CHARINDEX('*',@NumberStar)>0
begin
--判断a,b是否合法:844.5.5*1
set @a1=SUBSTRING(@NumberStar,0,CHARINDEX('*',@NumberStar))
set @b1=SUBSTRING(@NumberStar,CHARINDEX('*',@NumberStar)+1,len(@NumberStar)-CHARINDEX('*',@NumberStar))

if CHARINDEX('.',@a1)>0
begin
set @a1=SUBSTRING(@a1,CHARINDEX('.',@a1)+1,len(@a1)-CHARINDEX('.',@a1))
if CHARINDEX('.',@a1)>0
begin
return -1
end
end
if CHARINDEX('.',@b1)>0
begin
set @b1=SUBSTRING(@b1,CHARINDEX('.',@b1)+1,len(@b1)-CHARINDEX('.',@b1))
if CHARINDEX('.',@b1)>0
begin
return -1
end
end
set @a=SUBSTRING(@NumberStar,0,CHARINDEX('*',@NumberStar))
set @b=SUBSTRING(@NumberStar,CHARINDEX('*',@NumberStar)+1,len(@NumberStar)-CHARINDEX('*',@NumberStar))
set @retrunNum=@retrunNum+@a*@b
end
else
begin
--判断a,b是否合法:844.5.5*1
set @a1=@NumberStar
if CHARINDEX('.',@a1)>0
begin
set @a1=SUBSTRING(@a1,CHARINDEX('.',@a1)+1,len(@a1)-CHARINDEX('.',@a1))
if CHARINDEX('.',@a1)>0
begin
return -1
end
end
set @retrunNum=@retrunNum+@NumberStar
end
end
if CHARINDEX('*',@Number)>0
begin
set @a1=SUBSTRING(@Number,0,CHARINDEX('*',@Number))
set @b1=SUBSTRING(@Number,CHARINDEX('*',@Number)+1,len(@Number)-CHARINDEX('*',@Number))
--判断a,b是否合法:844.5.5*1
if CHARINDEX('.',@a1)>0
begin
set @a1=SUBSTRING(@a1,CHARINDEX('.',@a1)+1,len(@a1)-CHARINDEX('.',@a1))
if CHARINDEX('.',@a1)>0
begin
return -1
end
end
if CHARINDEX('.',@b1)>0
begin
set @b1=SUBSTRING(@b1,CHARINDEX('.',@b1)+1,len(@b1)-CHARINDEX('.',@b1))
if CHARINDEX('.',@b1)>0
begin
return -1
end
end
set @a=SUBSTRING(@Number,0,CHARINDEX('*',@Number))
set @b=SUBSTRING(@Number,CHARINDEX('*',@Number)+1,len(@Number)-CHARINDEX('*',@Number))
set @retrunNum=@retrunNum+@a*@b
end
else
begin
--判断a,b是否合法:844.5.5*1
set @a1=@Number
if CHARINDEX('.',@a1)>0
begin
set @a1=SUBSTRING(@a1,CHARINDEX('.',@a1)+1,len(@a1)-CHARINDEX('.',@a1))
if CHARINDEX('.',@a1)>0
begin
return -1
end
end
set @retrunNum=@retrunNum+@Number
end
end
return @retrunNum
end

 

转载于:https://www.cnblogs.com/dz-boss/p/8029124.html