January 20, 2006 at 5:18 am
The array function was the first thing that I always had needed and missed not having in SQL 2000. Unfortunatly as time does not permit me, I am probably the only one here who hasn't yet had the chance to explore 2005 so if what I'm posting here is now superseeded, I do apologize. However, I'm sure it will come in handy to some.
In my programming, there are many cases where I need to generate an array on the fly from a text string input. In asp, I'm able to do this by calling String("Some,String").split(",") but SQL also lacks this capability.
Through the use of UDFs I am able to simulate this functionality and make the full use of a psuedo array. I have included both a single array and a dual array. The dual array is very useful when you wish to submit a string of variables and their values to SQL either for storage and/or processing, such as "Item1=Value1&Item2=Value2"
I hope that this may prove useful to someone else out there and I thank all the authors who have made my life easier thanks to the posts/articles here!
-Anubis.
Usage:
SELECT ArrayItem
FROM [dbo].[Scripts_CreateArray]('Input,String,Here', ',')
Function:
CREATE FUNCTION [dbo].[Scripts_CreateArray]
(
@ItemInput [varchar] (8000),
@ItemBreaker [varchar] (5)
 
RETURNS @ArrayTable TABLE
(
ArrayItem [varchar] (200)-- (if collation required) COLLATE SQL_Latin1_General_CP1_CI_AS
 
AS
BEGIN
-- DECLARE WORKING VARIABLES
DECLARE @BreakerPosition [int],
@BreakerLength [tinyint],
@LoopCounter [int],
@LoopCounterMax [int]
SET @LoopCounter = 0
SET @LoopCounterMax = LEN(@ItemInput)
SET @BreakerLength = LEN(@ItemBreaker)
-- BEGIN LOOP FOR ARRAY ITEMS
WHILE (@LoopCounter < @LoopCounterMax AND LEN(@ItemInput) > 0)
BEGIN
-- GET BREAKER POSITION
SET @BreakerPosition = CHARINDEX(@ItemBreaker, @ItemInput)
IF (@BreakerPosition = 0)
BEGIN
SET @BreakerPosition = LEN(@ItemInput) + @BreakerLength
END
-- INSERT INTO TEMP TABLE
INSERT INTO @ArrayTable
(
ArrayItem
 
VALUES
(
SUBSTRING(@ItemInput, 1, (@BreakerPosition - 1))
 
-- UPDATE LENGTHS AND POSITIONS
SET @ItemInput = SUBSTRING(@ItemInput, (@BreakerPosition + @BreakerLength), LEN(@ItemInput))
SET @LoopCounter = @LoopCounter + 1
END
-- RETURN ARRAY
RETURN
END
Usage:
SELECT ArrayItem, ArrayItem2
FROM [dbo].[Scripts_CreateArray_Dual]('Input=Item,String=Here', '=', ',')
Function:
CREATE FUNCTION [dbo].[Scripts_CreateArray_Dual]
(
@ItemInput [varchar] (8000),
@ItemBreaker [varchar] (5),
@ItemBreaker2 [varchar] (5)
 
RETURNS @ArrayTable TABLE
(
ArrayItem [varchar](4000),-- COLLATE SQL_Latin1_General_CP1_CI_AS,
ArrayItem2 [varchar](4000)-- COLLATE SQL_Latin1_General_CP1_CI_AS
 
AS
BEGIN
-- DECLARE WORKING VARIABLES
DECLARE @BreakerPosition [int],
@BreakerLength [tinyint],
@BreakerLength2 [tinyint],
@LoopCounter [int],
@LoopCounterMax [int],
@Item1 [varchar](4000),
@Item2 [varchar](4000)
SET @LoopCounter = 0
SET @LoopCounterMax = LEN(@ItemInput)
SET @BreakerLength = LEN(@ItemBreaker)
SET @BreakerLength2 = LEN(@ItemBreaker2)
-- BEGIN LOOP FOR ARRAY ITEMS
WHILE (@LoopCounter < @LoopCounterMax AND LEN(@ItemInput) > 0)
BEGIN
-- GET BREAKER POSITION
SET @BreakerPosition = CHARINDEX(@ItemBreaker, @ItemInput)
IF (@BreakerPosition = 0)
BEGIN
SET @BreakerPosition = LEN(@ItemInput) + @BreakerLength
END
-- GET ITEM 1
SET @Item1 = SUBSTRING(@ItemInput, 1, (@BreakerPosition - 1))
-- UPDATE LENGTHS AND POSITIONS
SET @ItemInput = SUBSTRING(@ItemInput, (@BreakerPosition + @BreakerLength), LEN(@ItemInput))
SET @LoopCounter = @LoopCounter + 1
-- GET BREAKER POSITION
SET @BreakerPosition = CHARINDEX(@ItemBreaker2, @ItemInput)
IF (@BreakerPosition = 0)
BEGIN
SET @BreakerPosition = LEN(@ItemInput) + @BreakerLength
END
-- GET ITEM 2
SET @Item2 = SUBSTRING(@ItemInput, 1, (@BreakerPosition - 1))
-- UPDATE LENGTHS AND POSITIONS
SET @ItemInput = SUBSTRING(@ItemInput, (@BreakerPosition + @BreakerLength2), LEN(@ItemInput))
SET @LoopCounter = @LoopCounter + 1
-- INSERT INTO TEMP TABLE
INSERT INTO @ArrayTable
(
ArrayItem,
ArrayItem2
 
VALUES
(
@Item1,
@Item2
 
END
-- RETURN ARRAY
RETURN
END
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply