SQLStackEngine - simulate a character stack
SQLStackEngine - simulate a character stack using a string. This script was developed specifically to support the operator stack in SQLCalcEngine(also on this site). However, the implementation is complete enough to stand on its own.
There are several functions in the package that correspond to the common stack functions of Push, Pop, Peek, and so on.
In addition, unit test code is embedded in-line within the package as commented out lines.
--
-- SQLStackEngine
--
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'FN_StringStackDepth' AND type = 'FN')
DROP FUNCTION FN_StringStackDepth
GO
CREATE FUNCTION FN_StringStackDepth (
@InStack VARCHAR(500))
RETURNS NUMERIC
AS
BEGIN
RETURN (CASE
WHEN (@InStack IS NULL) THEN 0
WHEN (LEN(RTRIM(@InStack)) < 1) THEN 0
ELSE LEN(RTRIM(@InStack))
END)
END
GO
-- test and debug DECLARE @MyStack VARCHAR(500)
-- test and debug SET @MyStack = '*'
-- test and debug SELECT @MyStack, dbo.FN_StringStackDepth (@MyStack)
-- test and debug SET @MyStack = ''
-- test and debug SELECT @MyStack, dbo.FN_StringStackDepth (@MyStack)
-- test and debug SET @MyStack = NULL
-- test and debug SELECT @MyStack, dbo.FN_StringStackDepth (@MyStack)
-- test and debug SET @MyStack = '*+-/'
-- test and debug SELECT @MyStack, dbo.FN_StringStackDepth (@MyStack)
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'FN_StringStackIsEmpty' AND type = 'FN')
DROP FUNCTION FN_StringStackIsEmpty
GO
CREATE FUNCTION FN_StringStackIsEmpty (
@InStack VARCHAR(500))
RETURNS CHAR
AS
BEGIN
RETURN (CASE
WHEN (@InStack IS NULL) THEN 'T'
WHEN (LEN(RTRIM(@InStack)) < 1) THEN 'T'
ELSE 'F'
END)
END
GO
-- test and debug DECLARE @MyStack VARCHAR(500)
-- test and debug SET @MyStack = '*'
-- test and debug SELECT @MyStack, dbo.FN_StringStackIsEmpty (@MyStack)
-- test and debug SET @MyStack = ''
-- test and debug SELECT @MyStack, dbo.FN_StringStackIsEmpty (@MyStack)
-- test and debug SET @MyStack = NULL
-- test and debug SELECT @MyStack, dbo.FN_StringStackIsEmpty (@MyStack)
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'FN_StringStackPush' AND type = 'FN')
DROP FUNCTION FN_StringStackPush
GO
CREATE FUNCTION FN_StringStackPush (
@InStack VARCHAR(500),
@InChar CHAR)
RETURNS VARCHAR(500)
AS
BEGIN
RETURN (CASE
WHEN (@InStack IS NULL) THEN @InChar
ELSE (RTRIM(@InStack) + @InChar)
END)
END
GO
-- test and debug DECLARE @MyStack VARCHAR(500), @MyChar CHAR
-- test and debug SELECT '1', @MyStack
-- test and debug SET @MyChar = 'A'
-- test and debug SET @MyStack = dbo.FN_StringStackPush (@MyStack, @MyChar)
-- test and debug SELECT '2', @MyStack
-- test and debug SET @MyChar = 'B'
-- test and debug SET @MyStack = dbo.FN_StringStackPush (@MyStack, @MyChar)
-- test and debug SELECT '3', @MyStack
-- test and debug SET @MyChar = 'C'
-- test and debug SET @MyStack = dbo.FN_StringStackPush (@MyStack, @MyChar)
-- test and debug SELECT '4', @MyStack
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'FN_StringStackPeek' AND type = 'FN')
DROP FUNCTION FN_StringStackPeek
GO
CREATE FUNCTION FN_StringStackPeek (
@InStack VARCHAR(500),
@InPos INT)
RETURNS CHAR
AS
BEGIN
RETURN (CASE
WHEN (@InPos < 1) THEN NULL
WHEN (@InStack IS NULL) THEN NULL
WHEN (LEN(RTRIM(@InStack)) < @InPos) THEN NULL
ELSE SUBSTRING(RTRIM(@InStack), @InPos, 1)
END)
END
GO
-- test and debug DECLARE @MyStack VARCHAR(500)
-- test and debug SELECT '1', @MyStack,dbo.FN_StringStackPeek (@MyStack, 0)
-- test and debug SELECT '2', @MyStack,dbo.FN_StringStackPeek (@MyStack, 1)
-- test and debug SELECT '3', @MyStack,dbo.FN_StringStackPeek (@MyStack, 2)
-- test and debug SET @MyStack = 'A'
-- test and debug SELECT '4', @MyStack,dbo.FN_StringStackPeek (@MyStack, 0)
-- test and debug SELECT '5', @MyStack,dbo.FN_StringStackPeek (@MyStack, 1)
-- test and debug SELECT '6', @MyStack,dbo.FN_StringStackPeek (@MyStack, 2)
-- test and debug SET @MyStack = 'ABCDE'
-- test and debug SELECT '7', @MyStack,dbo.FN_StringStackPeek (@MyStack, 0)
-- test and debug SELECT '8', @MyStack,dbo.FN_StringStackPeek (@MyStack, 1)
-- test and debug SELECT '9', @MyStack,dbo.FN_StringStackPeek (@MyStack, 2)
-- test and debug SET @MyStack = ''
-- test and debug SELECT '10', @MyStack,dbo.FN_StringStackPeek (@MyStack, 0)
-- test and debug SELECT '11', @MyStack,dbo.FN_StringStackPeek (@MyStack, 1)
-- test and debug SELECT '12', @MyStack,dbo.FN_StringStackPeek (@MyStack, 2)
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'FN_StringStackTop' AND type = 'FN')
DROP FUNCTION FN_StringStackTop
GO
CREATE FUNCTION FN_StringStackTop (
@InStack VARCHAR(500))
RETURNS CHAR
AS
BEGIN
RETURN (CASE
WHEN (@InStack IS NULL) THEN NULL
WHEN (LEN(RTRIM(@InStack)) < 1) THEN NULL
ELSE RIGHT (RTRIM(@InStack), 1)
END)
END
GO
-- test and debug DECLARE @MyStack VARCHAR(500)
-- test and debug SELECT '1', @MyStack,dbo.FN_StringStackTop (@MyStack)
-- test and debug SET @MyStack = 'A'
-- test and debug SELECT '2', @MyStack,dbo.FN_StringStackTop (@MyStack)
-- test and debug SET @MyStack = 'ABCDE'
-- test and debug SELECT '3', @MyStack,dbo.FN_StringStackTop (@MyStack)
-- test and debug SET @MyStack = ''
-- test and debug SELECT '4', @MyStack,dbo.FN_StringStackTop (@MyStack)
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'FN_StringStackPop' AND type = 'FN')
DROP FUNCTION FN_StringStackPop
GO
CREATE FUNCTION FN_StringStackPop (
@InStack VARCHAR(500))
RETURNS VARCHAR(500)
AS
BEGIN
RETURN (CASE
WHEN (@InStack IS NULL) THEN NULL
WHEN (LEN(RTRIM(@InStack)) <= 1) THEN NULL
ELSE LEFT (RTRIM(@InStack), LEN(RTRIM(@InStack)) - 1)
END)
END
GO
-- test and debug DECLARE @MyStack VARCHAR(500)
-- test and debug SELECT '1', @MyStack, dbo.FN_StringStackPop (@MyStack)
-- test and debug SET @MyStack = 'A'
-- test and debug SELECT '2', @MyStack, dbo.FN_StringStackPop (@MyStack)
-- test and debug SET @MyStack = 'AB'
-- test and debug SELECT '3', @MyStack, dbo.FN_StringStackPop (@MyStack)
-- test and debug SET @MyStack = 'ABCDE'
-- test and debug SELECT '4', @MyStack, dbo.FN_StringStackPop (@MyStack)
-- test and debug SET @MyStack = ''
-- test and debug SELECT '5', @MyStack, dbo.FN_StringStackPop (@MyStack) 
