SQL中返回计算表达式的函数

今天在做一个查询的时候遇到这样一个问题，有一行数据为：

money　　number

 10-5　　      3

我要计算money*number的值，在网上找了很久，终于找一个了一个SQL中返回计算表达式的函数，但是但只能算加、减、乘、除，不能算幂。下面给大家分享一下：

SQL中返回计算表达式的函数

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*(10-5) ' )
/* 结果
--------------------
15.000000
*/

select dbo.GetExp( number + ' *( ' + money + ' ) ' ) from tb

 

 

 

 

 

转载于:https://www.cnblogs.com/zzj/archive/2010/02/04/1663974.html