自定义计算表达式的函数

CREATE FUNCTION dbo.GetExp(@pstrExpress AS VARCHAR(8000)) 
RETURNS DECIMAL(18,6) AS
BEGIN
DECLARE @i INT,@j INT
DECLARE @c1 CHAR(1),@c2 CHAR(1),@c VARCHAR(100)
DECLARE @v1 DECIMAL(18,6),@v2 DECIMAL(18,6),@v DECIMAL(18,6)
DECLARE @t TABLE(ID INT IDENTITY(1,1),s VARCHAR(100))
DECLARE @s TABLE(ID INT IDENTITY(1,1),s VARCHAR(100))
DECLARE @sv TABLE(ID INT IDENTITY(1,1),v DECIMAL(18,6))


SET @pstrExpress=REPLACE(@pstrExpress,'','')
SELECT @i=0,@j=LEN(@pstrExpress),@c2='',@c=''
WHILE @i<@j
BEGIN
SELECT @c1=@c2,@i=@i+1
SELECT @c2=SUBSTRING(@pstrExpress,@i,1)
IF CHARINDEX(@c2,'.0123456789') >0 or (@c2='-'and @c1 IN('','*','-','+','/','('))
BEGIN SELECT @c=@c+@c2 CONTINUE END
IF @c<>'' BEGIN INSERT @t(s) SELECT @c SELECT @c='' END
IF CHARINDEX(@c2,')')>0
BEGIN
INSERT @t(s) SELECT s FROM @s WHERE ID >ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0) ORDER BY ID DESC
DELETE @s WHERE ID >=ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0) 
CONTINUE
END
IF CHARINDEX(@c2,'+-)')>0
BEGIN
INSERT @t(s) SELECT s FROM @s WHERE ID >ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0) ORDER BY ID DESC
DELETE @s WHERE ID >ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0) 
IF @c2<>')' INSERT @s(s) SELECT @c2
CONTINUE
END
IF CHARINDEX(@c2,'*/')>0
BEGIN
INSERT @t(s) SELECT s FROM @s WHERE ID >ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(','+','-')),0) ORDER BY ID DESC
DELETE @s WHERE ID >ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(','+','-')),0) 
INSERT @s SELECT @c2
CONTINUE
END
IF CHARINDEX(@c2,'(')>0 INSERT @s SELECT @c2
END
IF @c<>'' INSERT @t(s) SELECT @c
INSERT @t(s) SELECT s FROM @s ORDER BY ID DESC
SELECT @i=0,@j=MAX(ID) FROM @t
WHILE @i<@j
BEGIN
SELECT @i=@i+1
SELECT @c=s FROM @t WHERE ID =@i
IF @c='(' CONTINUE
IF @c NOT IN('*','-','+','/') BEGIN INSERT @sv(v) SELECT CONVERT(float,@c) CONTINUE END
SELECT @v2=v FROM @sv DELETE @sv WHERE ID =(SELECT MAX(ID) FROM @sv)
SELECT @v1=v FROM @sv DELETE @sv WHERE ID =(SELECT MAX(ID) FROM @sv)
SELECT @v=CASE @c WHEN '+' THEN @v1 + @v2 WHEN '-' THEN @v1 - @v2
WHEN '*' THEN @v1*@v2 WHEN '/' THEN @v1/@v2 END
INSERT @sv(v) SELECT @v
END
SELECT @v=v FROM @sv
RETURN @v

END

－－－－

调用时：Select dbo.GetExp('3*(1+7))