Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

RE: Expand / Collapse
Author
Message
Posted Friday, January 20, 2006 5:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 27, 2011 5:04 AM
Points: 20, Visits: 2

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

 

Post #252294
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse