Tuesday, December 7, 2010

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

 

No comments:

Post a Comment