SQL中返回计算表达式的函数
今天在做一个查询的时候遇到这样一个问题,有一行数据为:
money number
10-5 3
我要计算money*number的值,在网上找了很久,终于找一个了一个SQL中返回计算表达式的函数,但是但只能算加、减、乘、除,不能算幂。下面给大家分享一下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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