SQLCalcEngine - evaluate numeric expression
SQLCalcEngine - evaluate numeric expression similar to the VB eval function.
The basic concept is to pass a string containing a numeric expression such as ((2+3)*(4/9)) and receive a numeric value back.
The algorithm is based loosely on material found at http://www.arstdesign.com/articles/expression_evaluation.html, as well as material found in the book "Writing Compilers and Interpreters" by Ronald Mak.
The next enhancement will be to implement two-character operators, as well as support the common SQL functions in the expressions. Look for updates to the script over the near future.
--
-- SQLCalcEngine
--
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'FN_Compute' AND type = 'FN')
DROP FUNCTION FN_Compute
GO
CREATE FUNCTION FN_Compute (
@LParm NUMERIC(16,6),
@RParm NUMERIC(16,6),
@Op CHAR)
RETURNS NUMERIC (16,6)
AS
BEGIN
RETURN (CASE @Op
WHEN '+' THEN @LParm + @RParm
WHEN '-' THEN @LParm - @RParm
WHEN '*' THEN @LParm * @RParm
WHEN '/' THEN (CASE WHEN @RParm = 0 THEN NULL ELSE @LParm / @RParm END)
ELSE NULL
END)
END
GO
-- test and debug SELECT dbo.FN_Compute (3,5,'*')
-- test and debug SELECT dbo.FN_Compute (3,0,'?')
-- test and debug SELECT dbo.FN_Compute (3,0,'/')
-- test and debug SELECT dbo.FN_Compute (3,2,'/')
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'FN_Precedence' AND type = 'FN')
DROP FUNCTION FN_Precedence
GO
CREATE FUNCTION FN_Precedence (
@Op CHAR)
RETURNS NUMERIC
AS
BEGIN
RETURN (CASE @Op
WHEN '*' THEN 2
WHEN '/' THEN 2
WHEN '+' THEN 1
WHEN '-' THEN 1
ELSE 0
END)
END
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'FN_Evaluate' AND type = 'FN')
DROP FUNCTION FN_Evaluate
GO
CREATE FUNCTION FN_Evaluate (
@InExpression VARCHAR(8000))
RETURNS NUMERIC (16,6)
AS
BEGIN
DECLARE
@OperandStack TABLE (GID NUMERIC IDENTITY (1,1) NOT NULL,
Operand VARCHAR(30))
DECLARE
@OperatorStack VARCHAR(500),
@Result NUMERIC (16,6),
-- for processing all expressions
@ExprLen smallint,
@StrPtr smallint,
@testChar varchar(1),
-- for working with OperandStack
@MaxOperandGID NUMERIC,
-- For working with operators and operands
@thisOperator CHAR,
@thisOperand VARCHAR(30),
@LeftOperand VARCHAR(30),
@RightOperand VARCHAR(30),
-- For evaluating parenthetic subexpressions
@ParenDepth NUMERIC,
@SubExpression VARCHAR(8000)
SET @ExprLen = LEN (@InExpression)
SET @StrPtr = 1
WHILE @StrPtr <= @ExprLen
BEGIN
SET @testChar = SUBSTRING(@InExpression, @StrPtr, 1)
IF @testChar IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', '.')
BEGIN -- handle a numeric value
SET @thisOperand = ''
WHILE @TestChar IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0','.')
BEGIN
SET @thisOperand = @thisOperand + @TestChar
SET @StrPtr = @StrPtr + 1
SET @TestChar = SUBSTRING(@InExpression, @StrPtr, 1)
END
INSERT INTO @OperandStack (Operand) VALUES (@thisOperand)
END
ELSE IF @testChar IN ('*', '/', '-', '+')
BEGIN -- handle an operator
WHILE (dbo.FN_StringStackIsEmpty (@OperatorStack) = 'F' AND
dbo.FN_Precedence (dbo.FN_StringStackTop(@OperatorStack)) >
dbo.FN_Precedence (@TestChar)
)
BEGIN
SET @thisOperator = dbo.FN_StringStackTop(@OperatorStack)
SET @OperatorStack = dbo.FN_StringStackPop(@OperatorStack)
SELECT @MaxOperandGID = MAX(GID) FROM @OperandStack
SELECT @RightOperand = Operand FROM @OperandStack WHERE GID = @MaxOperandGID
DELETE FROM @OperandStack WHERE GID = @MaxOperandGID
SELECT @MaxOperandGID = MAX(GID) FROM @OperandStack
SELECT @LeftOperand = Operand FROM @OperandStack WHERE GID = @MaxOperandGID
DELETE FROM @OperandStack WHERE GID = @MaxOperandGID
INSERT INTO @OperandStack (Operand) VALUES (dbo.FN_Compute (@LeftOperand, @RightOperand, @thisOperator))
END
SET @OperatorStack = dbo.FN_StringStackPush(@OperatorStack, @TestChar)
SET @StrPtr = @StrPtr + 1
END
ELSE IF @testChar = '('
BEGIN -- Recursive call to this routine
SET @ParenDepth = 1
SET @SubExpression = ''
-- Eat the LParen
SET @StrPtr = @StrPtr + 1
WHILE (@StrPtr <= @ExprLen AND
@ParenDepth > 0)
BEGIN
SET @TestChar = SUBSTRING(@InExpression, @StrPtr, 1)
IF @TestChar = '(' SET @ParenDepth = @ParenDepth + 1
IF @TestChar = ')' SET @ParenDepth = @ParenDepth - 1
IF @ParenDepth > 0 SET @SubExpression = @SubExpression + @TestChar
SET @StrPtr = @StrPtr + 1
END
INSERT INTO @OperandStack (Operand) VALUES (dbo.FN_Evaluate (@SubExpression))
END
END
WHILE (dbo.FN_StringStackIsEmpty (@OperatorStack) = 'F')
BEGIN
SET @thisOperator = dbo.FN_StringStackTop(@OperatorStack)
-- test and debug SET @Result = '<' + @OperatorStack + '>'
-- test and debug SET @Result = @thisOperator
SET @OperatorStack = dbo.FN_StringStackPop(@OperatorStack)
SELECT @MaxOperandGID = MAX(GID) FROM @OperandStack
SELECT @RightOperand = Operand FROM @OperandStack WHERE GID = @MaxOperandGID
DELETE FROM @OperandStack WHERE GID = @MaxOperandGID
SELECT @MaxOperandGID = MAX(GID) FROM @OperandStack
SELECT @LeftOperand = Operand FROM @OperandStack WHERE GID = @MaxOperandGID
DELETE FROM @OperandStack WHERE GID = @MaxOperandGID
INSERT INTO @OperandStack (Operand) VALUES (dbo.FN_Compute (@LeftOperand, @RightOperand, @thisOperator))
END
SELECT @Result = Operand FROM @OperandStack
-- test & debug SELECT @Result = 'OperatorStack = ' + RTRIM(@OperatorStack)
-- test & debug SELECT @Result = @Result + ' OperandStack = '
-- test & debug SELECT @Result = COALESCE(@Result + ',', '') + RTRIM(Operand) FROM @OperandStack ORDER BY GID
RETURN (@Result)
END
GO
-- test and debug DECLARE @MyExpression VARCHAR(500)
-- test and debug SET @MyExpression = '1+2'
-- test and debug SELECT @MyExpression,dbo.FN_Evaluate (@MyExpression), 'Should equal 3'
-- test and debug SET @MyExpression = '1+2+3'
-- test and debug SELECT @MyExpression,dbo.FN_Evaluate (@MyExpression), 'Should equal 6'
-- test and debug SET @MyExpression = '1+2+3*4.5'
-- test and debug SELECT @MyExpression,dbo.FN_Evaluate (@MyExpression), 'Should equal 16.5'
-- test and debug SET @MyExpression = '1+2+(3*4)'
-- test and debug SELECT @MyExpression,dbo.FN_Evaluate (@MyExpression), 'Should equal 15'
-- test and debug SET @MyExpression = '(1+2+(3*4))'
-- test and debug SELECT @MyExpression,dbo.FN_Evaluate (@MyExpression), 'Should equal 15'
-- test and debug SET @MyExpression = '(1+2)+((3*4)*2)'
-- test and debug SELECT @MyExpression,dbo.FN_Evaluate (@MyExpression), 'Should equal 27'
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'FN_ExpressionIsValid' AND type = 'FN')
DROP FUNCTION FN_ExpressionIsValid
GO
CREATE FUNCTION FN_ExpressionIsValid (
@InExpression VARCHAR(8000))
RETURNS VARCHAR(1)
AS
BEGIN
DECLARE
@OperandStack TABLE (GID NUMERIC IDENTITY (1,1) NOT NULL,
Operand VARCHAR(30))
DECLARE
@OperatorStack VARCHAR(500),
@IsValidInd VARCHAR(1),
@thisOperator VARCHAR(1),
-- for processing all expressions
@ExprLen SMALLINT,
@StrPtr SMALLINT,
@testChar VARCHAR(1)
SET @IsValidInd = 'T'
SET @ExprLen = LEN (@InExpression)
SET @StrPtr = 1
WHILE @StrPtr <= @ExprLen
AND @IsValidInd = 'T'
BEGIN
SET @testChar = SUBSTRING(@InExpression, @StrPtr, 1)
IF @testChar NOT IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', '.',
'*', '/', '-', '+',
'(', ')') SET @IsValidInd = 'F'
IF @IsValidInd = 'T'
BEGIN
IF @TestChar = '('
BEGIN
SET @OperatorStack = dbo.FN_StringStackPush(@OperatorStack, @TestChar)
END
ELSE IF @TestChar = ')'
BEGIN
IF dbo.FN_StringStackIsEmpty (@OperatorSTack) = 'T' SET @IsValidInd = 'F'
SET @thisOperator = dbo.FN_StringStackTop(@OperatorStack)
SET @OperatorStack = dbo.FN_StringStackPop(@OperatorStack)
IF @thisOperator != '(' SET @IsValidInd = 'F'
END
END
SET @StrPtr = @StrPtr + 1
END
IF @IsValidInd = 'T'
BEGIN
IF dbo.FN_StringStackIsEmpty (@OperatorSTack) = 'F' SET @IsValidInd = 'F'
END
RETURN (@IsValidInd)
END
GO
-- test and debug DECLARE @MyExpression VARCHAR(500)
-- test and debug SET @MyExpression = '1+2'
-- test and debug SELECT @MyExpression,dbo.FN_ExpressionIsValid (@MyExpression), 'Should equal T'
-- test and debug SET @MyExpression = '1+2+3'
-- test and debug SELECT @MyExpression,dbo.FN_ExpressionIsValid (@MyExpression), 'Should equal T'
-- test and debug SET @MyExpression = '1+2+3*4.5'
-- test and debug SELECT @MyExpression,dbo.FN_ExpressionIsValid (@MyExpression), 'Should equal T'
-- test and debug SET @MyExpression = '1+2+(3*4)'
-- test and debug SELECT @MyExpression,dbo.FN_ExpressionIsValid (@MyExpression), 'Should equal T'
-- test and debug SET @MyExpression = '(1+2+(3*4))'
-- test and debug SELECT @MyExpression,dbo.FN_ExpressionIsValid (@MyExpression), 'Should equal T'
-- test and debug SET @MyExpression = '(1+2)+((3*4)*2)'
-- test and debug SELECT @MyExpression,dbo.FN_ExpressionIsValid (@MyExpression), 'Should equal T'
-- test and debug SET @MyExpression = '((1+2)+((3*4)*2)'
-- test and debug SELECT @MyExpression,dbo.FN_ExpressionIsValid (@MyExpression), 'Should equal F'
-- test and debug SET @MyExpression = '(1+2)+((3*4)*2))'
-- test and debug SELECT @MyExpression,dbo.FN_ExpressionIsValid (@MyExpression), 'Should equal F'
-- test and debug SET @MyExpression = '(1+2)+((3*4))*2)'
-- test and debug SELECT @MyExpression,dbo.FN_ExpressionIsValid (@MyExpression), 'Should equal F'
-- test and debug SET @MyExpression = '(1+2)+((3m4)*2))'
-- test and debug SELECT @MyExpression,dbo.FN_ExpressionIsValid (@MyExpression), 'Should equal F' 
