Splitting an integer list in TSQL (See related posts)

Revised: 2006-02-23 - fixed various bugs by using new algorithm
Often times I have a list of integers I need to pass to the database to get worked on. Such as checkboxes on a web page or some other list. I needed some TSQL that would take a text string and split it by a separator, in this case a comma. The following is the result of that need.
The way I normally use it is in a stored procedure like the one below with several text type arguments. Sometimes I use a variation designed to split a list of strings separated by a special character sequence. Image two lists, one of the ids and one of the data. You parse the first list to get a table of the ids and you parse the second list to get the data and insert/update as appropriate.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[uspSplitIntegerList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE [dbo].[uspSplitIntegerList]
GO
                                      
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
-- uspSplitIntegerList
--
-- Description:
--		splits a comma separated list of integers and returns the integer list
--
-- Arguments:
--		@list_integers					- list of integers
--
-- Notes:
-- 02/22/2006 - WSR : use DATALENGTH instead of LEN throughout because LEN doesn't count trailing blanks
--
-- History:
-- 02/22/2006 - WSR : revised algorithm to account for items crossing 8000 character boundary
--
CREATE PROCEDURE uspSplitIntegerList
	@list_integers			text
AS

SET NOCOUNT ON

DECLARE @InputLen			integer			-- input text length
DECLARE @TextPos			integer			-- current position within input text
DECLARE @Chunk				varchar(8000)	-- chunk within input text
DECLARE @ChunkPos			integer			-- current position within chunk
DECLARE @DelimPos			integer			-- position of delimiter
DECLARE @ChunkLen			integer			-- chunk length
DECLARE @DelimLen			integer			-- delimiter length
DECLARE @Delimiter      varchar(3)		-- delimiter
DECLARE @ItemBegPos		integer			-- item starting position in text
DECLARE @ItemOrder		integer			-- item order in list

-- create table to hold list items
-- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied
CREATE TABLE #list_items ( item_order integer, item_begpos integer, item_endpos integer )

-- process list
IF @list_integers IS NOT NULL
   BEGIN

	-- initialize
   -- notice that this loop assumes a delimiter length of 1
   -- if the delimiter is longer we have to deal with stuff like delimiters straddling the chunk boundaries
   SET @InputLen = DATALENGTH(@list_integers)
   SET @TextPos = 1
	SET @Delimiter = ','
	SET @DelimLen = DATALENGTH(@Delimiter)
   SET @ItemBegPos = 1
   SET @ItemOrder = 1
   SET @ChunkLen = 1

   -- cycle through input processing chunks
   WHILE @TextPos <= @InputLen AND @ChunkLen <> 0
      BEGIN

      -- get current chunk
      SET @Chunk = SUBSTRING(@list_integers, @TextPos, 8000)

      -- setup initial variable values
      SET @ChunkPos = 1
      SET @ChunkLen = DATALENGTH(@Chunk)
      SET @DelimPos = CHARINDEX(@Delimiter, @Chunk, @ChunkPos)

      -- loop over the chunk, until the last delimiter
      WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0
         BEGIN

			-- insert position
         INSERT INTO #list_items (item_order, item_begpos, item_endpos)
         VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)
         
         -- adjust positions
         SET @ItemOrder = @ItemOrder + 1
         SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen
         SET @ChunkPos = @DelimPos + @DelimLen
      
         -- find next delimiter      
         SET @DelimPos = CHARINDEX(@Delimiter, @Chunk, @ChunkPos)

         END

      -- adjust positions
      SET @TextPos = @TextPos + @ChunkLen

      END

	-- handle last item
   IF @ItemBegPos <= @InputLen
      BEGIN

      -- insert position
      INSERT INTO #list_items (item_order, item_begpos, item_endpos)
      VALUES (@ItemOrder, @ItemBegPos, @InputLen)

      END

	-- delete the bad items
   DELETE FROM #list_items
   WHERE item_endpos < item_begpos

   -- return list items
	SELECT CAST(SUBSTRING(@list_integers, item_begpos, (item_endpos - item_begpos + 1)) AS integer) AS item_integer, item_order, item_begpos, item_endpos
   FROM #list_items
   WHERE ISNUMERIC(SUBSTRING(@list_integers, item_begpos, (item_endpos - item_begpos + 1))) = 1
   ORDER BY item_order

   END

DROP TABLE #list_items

RETURN

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO