split() function (TSQL) - pass and handle delimited strings

How to Pass and Handle a Delimited String similar to handling 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 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.)

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:



-- 1.)
SELECT *
FROM dbo.Split( ',', '11,23,3,14' ) AS s
ORDER BY cast( s.[Value] AS int )

-- 2.)
SELECT *
FROM dbo.Split( '', 'BobJaneMaryLiHsiaoLubor' ) AS s
ORDER BY s.[Value]

-- 3.)
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