Search Wiki:

How to Pass and Handle a Delimited String similar to handling an Array() of Values.

Arnie Rowland, March 26, 2008

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 a small array() using T-SQL. The client application creates a single-variate array(), and then converts that array() to a single string, and passes that string 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 will work with SQL 2000, 2005, 2008.)

  • This method is derived from previous work freely provided and widely distributed by Jens Suessmeyer, and is provided with full acknowledgement of his significant efforts on behalf of all those learning to use T-SQL.
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 similar to working with 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.
(Performance Notes)

Create Sample Data
-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create Table for  Sample Data 
CREATE TABLE #MyTable
   (  RowID    int  IDENTITY,
      LastName varchar(20),
      FirstName varchar(20)
    )
 
-- Load Sample Data
INSERT INTO #MyTable VALUES ( 'Davolio', 'Nancy' )
INSERT INTO #MyTable VALUES ( 'Fuller', 'Andrew' )
INSERT INTO #MyTable VALUES ( 'Leverling', 'Janet' )
INSERT INTO #MyTable VALUES ( 'Peacock', 'Margaret' )
INSERT INTO #MyTable VALUES ( 'Buchanan', 'Steven' )
INSERT INTO #MyTable VALUES ( 'Suyama', 'Michael' )
INSERT INTO #MyTable VALUES ( 'King', 'Robert' )
INSERT INTO #MyTable VALUES ( 'Callahan', 'Laura' )
INSERT INTO #MyTable VALUES ( 'Dodsworth', 'Anne' )    
Verify the Sample Data
SELECT 
   RowID,
   LastName,
   FirstName
FROM #MyTable
RowIDLastNameFirstName
1DavolioNancy
2FullerAndrew
3LeverlingJanet
4PeacockMargaret
5BuchananSteven
6SuyamaMichael
7KingRobert
8CallahanLaura
9DodsworthAnne

Return to Top

This User Defined Function works with SQL Server Versions 2000, 2005, 2008
  • This UDF is derived from Jens Suessmeyer's SPLIT() Function.
--Creates an 'InLine' Table Valued Function (TVF)
CREATE FUNCTION dbo.Split 
   (  @Delimiter varchar(5), 
      @List      varchar(8000)
   ) 
   RETURNS @TableOfValues table 
      (  RowID   smallint IDENTITY(1,1), 
         [Value] varchar(50) 
      ) 
AS 
   BEGIN
    
      DECLARE @LenString int 
 
      WHILE len( @List ) > 0 
         BEGIN 
         
            SELECT @LenString = 
               (CASE charindex( @Delimiter, @List ) 
                   WHEN 0 THEN len( @List ) 
                   ELSE ( charindex( @Delimiter, @List ) -1 )
                END
               ) 
                                
            INSERT INTO @TableOfValues 
               SELECT substring( @List, 1, @LenString )
                
            SELECT @List = 
               (CASE ( len( @List ) - @LenString ) 
                   WHEN 0 THEN '' 
                   ELSE right( @List, len( @List ) - @LenString - 1 ) 
                END
               ) 
         END
          
      RETURN 
      
   END 

Usage Examples

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.Split( ',', '11,23,3,14' ) AS s
   ORDER BY cast( s.[Value] AS int )
RowIDValue
33
111
414
223

Usage with the String Containing Character values.
   SELECT * 
   FROM dbo.Split( '|', 'Bob|Jane|Mary|Li|Hsiao|Lubor' ) AS s
   ORDER BY s.[Value]
RowIDValue
1Bob
5Hsiao
2Jane
4Li
6Lubor
3Mary

Return to Top


Using the Split Function in a JOIN to retrieve rows from a Table with 'matching' Values.
The variable @MyList could easily be an input Parameter to a Stored Procedure
DECLARE @MyList varchar(50)
 
SET @MyList = ( '2,4,7,8' )
 
SELECT 
   m.RowID,
   m.LastName,
   m.FirstName
FROM #MyTable AS m
   JOIN dbo.Split( ',', @MyList ) AS l
      ON m.RowID = cast( l.[Value] AS int )
ORDER BY 
   m.LastName,
   m.FirstName
Results: Only the Rows with RowID values that Match the list values are returned.
RowIDLastNameFirstName
8CallahanLaura
2FullerAndrew
7KingRobert
4PeacockMargaret

Return to Top

Clean up the test environment
DROP FUNCTION dbo.Split
DROP TABLE #MyTable
Return to Top


Performance Notes
There are many different methods of accomplishing this task floating around the internet. A couple of the more popular variations involve either passing in XML, or using a Common Table Expression (limited to SQL2005/2008) in a function. After testing with SQL Server 2008, in iterations of 10, the SPLIT() function above contrasted with a CTE based function, performed consistantly 4 times faster, and in iterations of 100, the SPLIT() function performed 10-12 times faster. The CTE function is limited to 100 items in the input string due to the maximum levels of recursion allowed. -I have NOT tested a CLR Function. I have not tested this method with large simulated arrays.

Test Code
SET NOCOUNT ON
 
DECLARE 
   @Start   time(7),
   @End     time(7),
   @Counter int
   
SELECT 
   @Counter = 0,
   @Start   = convert ( time, sysdatetime() )
 
WHILE @Counter < 10
   BEGIN
      SELECT 1 from dbo.split(',', '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
                                   31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,
                                   60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,
                                   89,90,91,92,93,94,95,96,97,98,99,100' )
      SET @Counter += 1
   END
   
SET @End = CONVERT( time, sysdatetime() )
PRINT 'This Method took ' + cast( datediff( ns, @Start, @End ) as varchar(20)) + ' nanoseconds.'

Additional Resources
Arrays and Lists, Erland Sommarskog
http://www.sommarskog.se/arrays-in-sql-2005.html
Create a Comma Delimited List from a Column in a Table.

Return to Top

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 26, 2008
Last edited Apr 25 2008 at 7:34 PM  by ArnieRowland, version 33
Comments
KevinMc wrote  Sep 4 2008 at 10:10 PM  
Any suggestions if the column you want to join to to retrieve matching values is a uniqueidentifier column?

Jeff_Moden wrote  Dec 29 2008 at 3:00 AM  
I agree... No XML... no CTE... but, no... no WHILE loops either, please. Please refer to the following link...
http://www.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden

FHankFreeman wrote  Mar 25 2013 at 12:37 AM  
Arnie, Jeff and others. This is still an excellent post and I was able to save tons of time by reading this well written post.. I am writing a new set of Database, where the new Indexes requests are in a SharePoint tracker.. Some of the indexes have INCLUDE columns in one of the fields and used your examples to split the string of data. All of the close to 200 indexes are T-SQL Script generated from the SharePoint columns, so it is internal automatic and user-friendly.

-------------------------------------
Declare @String_in varchar(250)
set @String_in = 'ServiceOrderID, AncillaryChargeTypeID,DeliveryGroupID, MarketerID, RelativeBillMonth_YYYYMM, ServiceOrderID, ServiceOrderTypeID, TariffScheduleID'
SELECT cast(max(RowID) as varchar(2)) FROM dbo.Split ( ',', @String_in ) ORDER BY 1
----
SELECT * FROM dbo.Split ( ',', @String_in ) ORDER BY 1
go

In this index [TST_idx2_APP_AncillaryServicesDetail_AccountID_(8)_INC-Cols], I used the syntax to get the column count and the list of columns. Please note that in the following syntax I do not list the INCLUDE columns just the literal '(n)_INC_Cols' where 'n' is the number of included columns. Yes there really isn't enough space to go into to much details, but the BIG Message here is you post gave me the insight to get done something eloquently and concisely in my code, which is important to me...

Soooo MUCHO Thanks !!!
Hank Freeman - Senior SQL DBA/Data Architect - Metro Atlanta, GA
hfreeman@msn.com

use [GCMA]; CREATE NONCLUSTERED INDEX [TST_idx2_APP_AncillaryServicesDetail_AccountID_(8)_INC-Cols]
ON [GCMA].[APP].[AncillaryServicesDetail]
(AccountID)
INCLUDE (ServiceOrderID, AncillaryChargeTypeID,DeliveryGroupID, MarketerID, RelativeBillMonth_YYYYMM, MainServiceOrderID, ServiceOrderTypeID, TariffScheduleID)
WITH.....
---------- end --- end ----

Updating...
Page view tracker