DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Splitting A Text List In Sql

02.24.2006
| 7549 views |
  • submit to reddit
        This goes along with my integer split procedure.
http://www.bigbold.com/snippets/posts/show/774

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.  This is 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.
http://www.bigbold.com/snippets/posts/show/774

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


/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
-- uspSplitTextList
--
-- Description:
--		splits a separated list of text items and returns the text items
--
-- Arguments:
--		@list_text				- list of text items
--		@Delimiter				- delimiter
--
-- 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 uspSplitTextList
	@list_text				text,
   @Delimiter				varchar(3)
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 @ItemBegPos		integer			-- item starting position in text
DECLARE @ItemOrder		integer			-- item order in list
DECLARE @DelimChar		varchar(1)		-- first character of delimiter (simple delimiter)

-- 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_text IS NOT NULL
   BEGIN

	-- initialize
   SET @InputLen = DATALENGTH(@list_text)
   SET @TextPos = 1
	SET @DelimChar = SUBSTRING(@Delimiter, 1, 1)
	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_text, @TextPos, 8000)

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

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

			-- see if this is a full delimiter
         IF SUBSTRING(@list_text, (@TextPos + @DelimPos - 1), @DelimLen) = @Delimiter
            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

				END
         ELSE
            BEGIN

            -- adjust positions
            SET @ChunkPos = @DelimPos + 1

            END
      
         -- find next delimiter      
         SET @DelimPos = CHARINDEX(@DelimChar, @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 SUBSTRING(@list_text, item_begpos, (item_endpos - item_begpos + 1)) AS item_text, item_order, item_begpos, item_endpos
   FROM #list_items
   ORDER BY item_order

   END

DROP TABLE #list_items

RETURN

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

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO