Never been to DZone Snippets before?

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

About this user

Will Rickards http://willrickards.net/

« Newer Snippets
Older Snippets »
Showing 1-2 of 2 total  RSS 

splitting a text list in sql

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

   1  
   2  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[uspSplitTextList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   3     DROP PROCEDURE [dbo].[uspSplitTextList]
   4  GO
   5                                        
   6  SET QUOTED_IDENTIFIER ON 
   7  GO
   8  SET ANSI_NULLS ON 
   9  GO
  10  
  11  
  12  /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
  13  -- uspSplitTextList
  14  --
  15  -- Description:
  16  --		splits a separated list of text items and returns the text items
  17  --
  18  -- Arguments:
  19  --		@list_text				- list of text items
  20  --		@Delimiter				- delimiter
  21  --
  22  -- Notes:
  23  -- 02/22/2006 - WSR : use DATALENGTH instead of LEN throughout because LEN doesn't count trailing blanks
  24  --
  25  -- History:
  26  -- 02/22/2006 - WSR : revised algorithm to account for items crossing 8000 character boundary
  27  --
  28  CREATE PROCEDURE uspSplitTextList
  29  	@list_text				text,
  30     @Delimiter				varchar(3)
  31  AS
  32  
  33  SET NOCOUNT ON
  34  
  35  DECLARE @InputLen			integer			-- input text length
  36  DECLARE @TextPos			integer			-- current position within input text
  37  DECLARE @Chunk				varchar(8000)	-- chunk within input text
  38  DECLARE @ChunkPos			integer			-- current position within chunk
  39  DECLARE @DelimPos			integer			-- position of delimiter
  40  DECLARE @ChunkLen			integer			-- chunk length
  41  DECLARE @DelimLen			integer			-- delimiter length
  42  DECLARE @ItemBegPos		integer			-- item starting position in text
  43  DECLARE @ItemOrder		integer			-- item order in list
  44  DECLARE @DelimChar		varchar(1)		-- first character of delimiter (simple delimiter)
  45  
  46  -- create table to hold list items
  47  -- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied
  48  CREATE TABLE #list_items ( item_order integer, item_begpos integer, item_endpos integer )
  49  
  50  -- process list
  51  IF @list_text IS NOT NULL
  52     BEGIN
  53  
  54  	-- initialize
  55     SET @InputLen = DATALENGTH(@list_text)
  56     SET @TextPos = 1
  57  	SET @DelimChar = SUBSTRING(@Delimiter, 1, 1)
  58  	SET @DelimLen = DATALENGTH(@Delimiter)
  59     SET @ItemBegPos = 1
  60     SET @ItemOrder = 1
  61     SET @ChunkLen = 1
  62  
  63     -- cycle through input processing chunks
  64     WHILE @TextPos <= @InputLen AND @ChunkLen <> 0
  65        BEGIN
  66  
  67        -- get current chunk
  68        SET @Chunk = SUBSTRING(@list_text, @TextPos, 8000)
  69  
  70        -- setup initial variable values
  71        SET @ChunkPos = 1
  72        SET @ChunkLen = DATALENGTH(@Chunk)
  73        SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)
  74  
  75        -- loop over the chunk, until the last delimiter
  76        WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0
  77           BEGIN
  78  
  79  			-- see if this is a full delimiter
  80           IF SUBSTRING(@list_text, (@TextPos + @DelimPos - 1), @DelimLen) = @Delimiter
  81              BEGIN
  82  
  83  				-- insert position
  84  	         INSERT INTO #list_items (item_order, item_begpos, item_endpos)
  85  	         VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)
  86  	         
  87  	         -- adjust positions
  88  	         SET @ItemOrder = @ItemOrder + 1
  89  	         SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen
  90  	         SET @ChunkPos = @DelimPos + @DelimLen
  91  
  92  				END
  93           ELSE
  94              BEGIN
  95  
  96              -- adjust positions
  97              SET @ChunkPos = @DelimPos + 1
  98  
  99              END
 100        
 101           -- find next delimiter      
 102           SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)
 103  
 104           END
 105  
 106        -- adjust positions
 107        SET @TextPos = @TextPos + @ChunkLen
 108  
 109        END
 110  
 111  	-- handle last item
 112     IF @ItemBegPos <= @InputLen
 113        BEGIN
 114  
 115        -- insert position
 116        INSERT INTO #list_items (item_order, item_begpos, item_endpos)
 117        VALUES (@ItemOrder, @ItemBegPos, @InputLen)
 118  
 119        END
 120  
 121  	-- delete the bad items
 122     DELETE FROM #list_items
 123     WHERE item_endpos < item_begpos
 124  
 125     -- return list items
 126  	SELECT SUBSTRING(@list_text, item_begpos, (item_endpos - item_begpos + 1)) AS item_text, item_order, item_begpos, item_endpos
 127     FROM #list_items
 128     ORDER BY item_order
 129  
 130     END
 131  
 132  DROP TABLE #list_items
 133  
 134  RETURN
 135  
 136  /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
 137  
 138  GO
 139  SET QUOTED_IDENTIFIER OFF 
 140  GO
 141  SET ANSI_NULLS ON 
 142  GO
to sql text list sql_server split by Will_Rickards on Feb 23, 2006

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.

   1  
   2  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[uspSplitIntegerList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   3     DROP PROCEDURE [dbo].[uspSplitIntegerList]
   4  GO
   5                                        
   6  SET QUOTED_IDENTIFIER ON 
   7  GO
   8  SET ANSI_NULLS ON 
   9  GO
  10  
  11  
  12  /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
  13  -- uspSplitIntegerList
  14  --
  15  -- Description:
  16  --		splits a comma separated list of integers and returns the integer list
  17  --
  18  -- Arguments:
  19  --		@list_integers					- list of integers
  20  --
  21  -- Notes:
  22  -- 02/22/2006 - WSR : use DATALENGTH instead of LEN throughout because LEN doesn't count trailing blanks
  23  --
  24  -- History:
  25  -- 02/22/2006 - WSR : revised algorithm to account for items crossing 8000 character boundary
  26  --
  27  CREATE PROCEDURE uspSplitIntegerList
  28  	@list_integers			text
  29  AS
  30  
  31  SET NOCOUNT ON
  32  
  33  DECLARE @InputLen			integer			-- input text length
  34  DECLARE @TextPos			integer			-- current position within input text
  35  DECLARE @Chunk				varchar(8000)	-- chunk within input text
  36  DECLARE @ChunkPos			integer			-- current position within chunk
  37  DECLARE @DelimPos			integer			-- position of delimiter
  38  DECLARE @ChunkLen			integer			-- chunk length
  39  DECLARE @DelimLen			integer			-- delimiter length
  40  DECLARE @Delimiter      varchar(3)		-- delimiter
  41  DECLARE @ItemBegPos		integer			-- item starting position in text
  42  DECLARE @ItemOrder		integer			-- item order in list
  43  
  44  -- create table to hold list items
  45  -- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied
  46  CREATE TABLE #list_items ( item_order integer, item_begpos integer, item_endpos integer )
  47  
  48  -- process list
  49  IF @list_integers IS NOT NULL
  50     BEGIN
  51  
  52  	-- initialize
  53     -- notice that this loop assumes a delimiter length of 1
  54     -- if the delimiter is longer we have to deal with stuff like delimiters straddling the chunk boundaries
  55     SET @InputLen = DATALENGTH(@list_integers)
  56     SET @TextPos = 1
  57  	SET @Delimiter = ','
  58  	SET @DelimLen = DATALENGTH(@Delimiter)
  59     SET @ItemBegPos = 1
  60     SET @ItemOrder = 1
  61     SET @ChunkLen = 1
  62  
  63     -- cycle through input processing chunks
  64     WHILE @TextPos <= @InputLen AND @ChunkLen <> 0
  65        BEGIN
  66  
  67        -- get current chunk
  68        SET @Chunk = SUBSTRING(@list_integers, @TextPos, 8000)
  69  
  70        -- setup initial variable values
  71        SET @ChunkPos = 1
  72        SET @ChunkLen = DATALENGTH(@Chunk)
  73        SET @DelimPos = CHARINDEX(@Delimiter, @Chunk, @ChunkPos)
  74  
  75        -- loop over the chunk, until the last delimiter
  76        WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0
  77           BEGIN
  78  
  79  			-- insert position
  80           INSERT INTO #list_items (item_order, item_begpos, item_endpos)
  81           VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)
  82           
  83           -- adjust positions
  84           SET @ItemOrder = @ItemOrder + 1
  85           SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen
  86           SET @ChunkPos = @DelimPos + @DelimLen
  87        
  88           -- find next delimiter      
  89           SET @DelimPos = CHARINDEX(@Delimiter, @Chunk, @ChunkPos)
  90  
  91           END
  92  
  93        -- adjust positions
  94        SET @TextPos = @TextPos + @ChunkLen
  95  
  96        END
  97  
  98  	-- handle last item
  99     IF @ItemBegPos <= @InputLen
 100        BEGIN
 101  
 102        -- insert position
 103        INSERT INTO #list_items (item_order, item_begpos, item_endpos)
 104        VALUES (@ItemOrder, @ItemBegPos, @InputLen)
 105  
 106        END
 107  
 108  	-- delete the bad items
 109     DELETE FROM #list_items
 110     WHERE item_endpos < item_begpos
 111  
 112     -- return list items
 113  	SELECT CAST(SUBSTRING(@list_integers, item_begpos, (item_endpos - item_begpos + 1)) AS integer) AS item_integer, item_order, item_begpos, item_endpos
 114     FROM #list_items
 115     WHERE ISNUMERIC(SUBSTRING(@list_integers, item_begpos, (item_endpos - item_begpos + 1))) = 1
 116     ORDER BY item_order
 117  
 118     END
 119  
 120  DROP TABLE #list_items
 121  
 122  RETURN
 123  
 124  /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
 125  
 126  GO
 127  SET QUOTED_IDENTIFIER OFF 
 128  GO
 129  SET ANSI_NULLS ON 
 130  GO
to sql text list integer sql_server split by Will_Rickards on Sep 30, 2005
« Newer Snippets
Older Snippets »
Showing 1-2 of 2 total  RSS