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
