RE:

  • 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)

    &nbsp 

    RETURNS @ArrayTable TABLE

     (

     ArrayItem [varchar] (200)-- (if collation required) COLLATE SQL_Latin1_General_CP1_CI_AS

    &nbsp

    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

    &nbsp

        VALUES

     (

     SUBSTRING(@ItemInput, 1, (@BreakerPosition - 1))

    &nbsp

     

        -- 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)

    &nbsp 

    RETURNS @ArrayTable TABLE

     (

     ArrayItem [varchar](4000),-- COLLATE SQL_Latin1_General_CP1_CI_AS,

     ArrayItem2 [varchar](4000)-- COLLATE SQL_Latin1_General_CP1_CI_AS

    &nbsp

    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

    &nbsp

        VALUES

     (

     @Item1,

     @Item2

    &nbsp

      END

     

    -- RETURN ARRAY

    RETURN

    END

     

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply