[MSSQL][Library] How to handle a Delimited String similar to handle an Array() of Values

Often there is the desire to pass data to T-SQL as an array() of values, yet T-SQL does not provide an array() datatype.

The following presents one method to simulate handling an array() using T-SQL. The client application creates a single-variate array(), and then passes that array() as a VARCHAR() parameter to a SQL Stored Procedure. The Stored Procedure, in turn, passes that VARCHAR(), or string, value to a User Defined Function that parses the string into a temporary table, and provides that temporary table back to the Stored Procedure to use as though it were a real table.

This 'method' can be used to separate a List of Values into Discrete values, and then use that List of Discrete Values in most operations like a Table.
It can be used in a SELECT statement, a multi-table JOIN, and in WHERE clause criteria.

The User may Specify any character as Delimiters.

     Examples:

          '1,2,35,101'                      (Comma Delimited)
          'Act1|Act2|Act5'                (Vertical Bar Delimited)
          '12345 12432 3234 452'     (Single Space Delimited)

Note: For handling very large amounts of data in a 'simulated' Array(), or for rapid multiple calls to the User Defined Function, it may be more efficient to consider creating a CLR Function using C#/VB and Regular Expressions. For small simulated arrays(), there may be little, if any, performance difference.

USE []

GO

IF OBJECT_ID(N'udf__Split') IS NOT NULL

  DROP FUNCTION udf__Split

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

-- *****************************************************************************

-- Name         : udf__Split

-- Author       : Chandra Gunawan

-- Date         : 06-Dec-2010

-- Description  : Convert comma seperated list of item to table

-- Parameters   :

--     @pList       vchr(8000)  Delimited list of item to split

--                                format:  item1[<dlm>[item2]...]

--                                example: a,b,c

--     @pDelimiter  vchr(1)     Delimiter (dlm)
--                                example: ,

--

-- Usage Sample :

--   -------------------------------------------------

--   SELECT * FROM dbo.udf__Split('a,b,c', ',')

--   -------------------------------------------------

--

-- Maintenance Log

-- =============================================================================

-- Date        ID     Description

-- -----------------------------------------------------------------------------

-- 02-Jun-09   chg    Initial Version

-- *****************************************************************************

CREATE FUNCTION dbo.udf__Split

(

    @pList      varchar(8000),

    @pDelimiter varchar(1) = ','

)

RETURNS @pTable TABLE

(

    id   smallint IDENTITY(1,1),

    item varchar(1000)

)

BEGIN 

  DECLARE @iSpot smallint,

          @cStr  varchar(8000),

          @cSql  varchar(8000) 

    

  IF ISNULL(@pDelimiter, '') = '' SET @pDelimiter = ','

  WHILE @pList <> '' 

  BEGIN 

    SET @iSpot = CHARINDEX(@pDelimiter, @pList) 

    IF @iSpot > 0 

    BEGIN 

      SET @cStr = LEFT(@pList, @iSpot - 1) 

      SET @pList = RIGHT(@pList, len(@pList) - @iSpot) 

    END 

    ELSE BEGIN 

      SET @cStr = @pList 

      SET @pList = '' 

    END 

    INSERT @pTable SELECT @cStr

  END 

  RETURN

END

GO

GRANT SELECT ON udf__Split TO public

GO

Usage Sample
The string values are parsed into individual 'rows' and then sorted.

Usage with the String Containing Numeric values.
(Note the use of CAST() in the ORDER BY clause to cause the Values to sort numerically.)  

SELECT *

  FROM dbo.udf__Split('11,23,3,14', ',') AS s

  ORDER BY cast(s.[item] AS int)

id	item
3	3
1	11
4	14
2	23

Usage with the String Containing Character values.

SELECT *

  FROM dbo.udf__Split( 'Bob|Jane|Mary|Li|Hsiao|Lubor', '|' ) AS s

  ORDER BY s.[item]

 

id	item
1	Bob
5	Hsiao
2	Jane
4	Li
6	Lubor
3	Mary

To get the second entry of the list.

DECLARE @cItem varchar(100)

SELECT @cItem = item

  FROM dbo.udf__Split( 'Bob|Jane|Mary|Li|Hsiao|Lubor', '|' )

  WHERE id = 2

SELECT @cItem

(No column name)

Jane

See also:
- Convert comma separated list of item into single column table (udf__ListToSingleColumn)