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
See also:
- Convert comma separated list of item into single column table (udf__ListToSingleColumn)
