SQL Server中计算表达式的和

项目使用的是SQL Server数据库，需要做一个审核规则，字段A中表达式的值和字段B中的值，做比较：
需求本身很简单，但是表达式中存在很多非法字符（非法全角，运算符，汉字……）
eg：1.1.1*2；1*+2，……
因此需要判断，否则直接运算，会报异常
具体SQL如下：
  1 ALTER FUNCTION [dbo].[getNumByExpressions]( @Number nvarchar(500))
  2 RETURNS numeric(10,4) 
  3 --返回-1，表示字符串存在问题
  4 --else返回其计算结果
  5 AS
  6 begin
  7 declare @retrunNum numeric(10,4)
  8 declare @NumberStar nvarchar(500)
  9 declare @a numeric(10,4)
 10 declare @b numeric(10,4)
 11 
 12 declare @a1 nvarchar(500)
 13 declare @b1 nvarchar(500)
 14 
 15 declare @Numberillegal nvarchar(500)
 16 declare @Numberdouble nvarchar(500)
 17 
 18 set @retrunNum=0
 19 --判断非法字符
 20 if PATINDEX('%[^0123456789+*.]%', @Number)>0
 21 begin
 22 return -1
 23 end
 24 --判断2个运算符相连接
 25 else if CHARINDEX('..',@Number)>0 or CHARINDEX('**',@Number)>0 or CHARINDEX('++',@Number)>0 or 
 26 CHARINDEX('*.',@Number)>0 or CHARINDEX('.*',@Number)>0 or CHARINDEX('+.',@Number)>0 or 
 27 CHARINDEX('.+',@Number)>0 or CHARINDEX('*+',@Number)>0 or CHARINDEX('+*',@Number)>0 or rtrim(ltrim(@Number))=''
 28 begin
 29 return -1
 30 end
 31 --判断运算是否在开头和结尾
 32 else if left(@Number,1)='.' or left(@Number,1)='*' or left(@Number,1)='+' or 
 33 right(@Number,1)='.' or right(@Number,1)='*' or right(@Number,1)='+' 
 34 begin
 35 return -1
 36 end
 37 else
 38 begin
 39 --数字全角判断
 40 set @Numberdouble=@Number
 41 while isnull(len(@Numberdouble),0)>0
 42 begin
 43 if ascii(left(@Numberdouble,1))=163
 44 begin
 45 return -1
 46 end
 47 if len(@Numberdouble)>1
 48 begin
 49 set @Numberdouble = right(@Numberdouble,len(@Numberdouble)-1)
 50 end
 51 else
 52 begin
 53 set @Numberdouble=null
 54 end
 55 
 56 end
 57 --计算结果
 58 while CHARINDEX('+',@Number)>0
 59 begin
 60 set @NumberStar = SUBSTRING(@Number,0,CHARINDEX('+',@Number))
 61 set @Number=SUBSTRING(@Number,CHARINDEX('+',@Number)+1,len(@Number)-CHARINDEX('+',@Number))
 62 if CHARINDEX('*',@NumberStar)>0
 63 begin
 64 --判断a,b是否合法:844.5.5*1
 65 set @a1=SUBSTRING(@NumberStar,0,CHARINDEX('*',@NumberStar))
 66 set @b1=SUBSTRING(@NumberStar,CHARINDEX('*',@NumberStar)+1,len(@NumberStar)-CHARINDEX('*',@NumberStar))
 67 
 68 if CHARINDEX('.',@a1)>0
 69 begin
 70 set @a1=SUBSTRING(@a1,CHARINDEX('.',@a1)+1,len(@a1)-CHARINDEX('.',@a1))
 71 if CHARINDEX('.',@a1)>0
 72 begin
 73 return -1
 74 end
 75 end
 76 if CHARINDEX('.',@b1)>0
 77 begin
 78 set @b1=SUBSTRING(@b1,CHARINDEX('.',@b1)+1,len(@b1)-CHARINDEX('.',@b1))
 79 if CHARINDEX('.',@b1)>0
 80 begin
 81 return -1
 82 end
 83 end
 84 set @a=SUBSTRING(@NumberStar,0,CHARINDEX('*',@NumberStar))
 85 set @b=SUBSTRING(@NumberStar,CHARINDEX('*',@NumberStar)+1,len(@NumberStar)-CHARINDEX('*',@NumberStar))
 86 set @retrunNum=@retrunNum+@a*@b
 87 end
 88 else 
 89 begin
 90 --判断a,b是否合法:844.5.5*1
 91 set @a1=@NumberStar
 92 if CHARINDEX('.',@a1)>0
 93 begin
 94 set @a1=SUBSTRING(@a1,CHARINDEX('.',@a1)+1,len(@a1)-CHARINDEX('.',@a1))
 95 if CHARINDEX('.',@a1)>0
 96 begin
 97 return -1
 98 end
 99 end
100 set @retrunNum=@retrunNum+@NumberStar
101 end
102 end
103 if CHARINDEX('*',@Number)>0
104 begin
105 set @a1=SUBSTRING(@Number,0,CHARINDEX('*',@Number))
106 set @b1=SUBSTRING(@Number,CHARINDEX('*',@Number)+1,len(@Number)-CHARINDEX('*',@Number))
107 --判断a,b是否合法:844.5.5*1
108 if CHARINDEX('.',@a1)>0
109 begin
110 set @a1=SUBSTRING(@a1,CHARINDEX('.',@a1)+1,len(@a1)-CHARINDEX('.',@a1))
111 if CHARINDEX('.',@a1)>0
112 begin
113 return -1
114 end
115 end
116 if CHARINDEX('.',@b1)>0
117 begin
118 set @b1=SUBSTRING(@b1,CHARINDEX('.',@b1)+1,len(@b1)-CHARINDEX('.',@b1))
119 if CHARINDEX('.',@b1)>0
120 begin
121 return -1
122 end
123 end
124 set @a=SUBSTRING(@Number,0,CHARINDEX('*',@Number))
125 set @b=SUBSTRING(@Number,CHARINDEX('*',@Number)+1,len(@Number)-CHARINDEX('*',@Number))
126 set @retrunNum=@retrunNum+@a*@b
127 end
128 else
129 begin
130 --判断a,b是否合法:844.5.5*1
131 set @a1=@Number
132 if CHARINDEX('.',@a1)>0
133 begin
134 set @a1=SUBSTRING(@a1,CHARINDEX('.',@a1)+1,len(@a1)-CHARINDEX('.',@a1))
135 if CHARINDEX('.',@a1)>0
136 begin
137 return -1
138 end
139 end
140 set @retrunNum=@retrunNum+@Number
141 end
142 end
143 return @retrunNum
144 end