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
