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'

Rate

Share

Share

Rate