Splitting an integer list in TSQL
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.
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
