Parsing Large Delimited Text String

Coop64 · January 22nd, 2004, 11:31 AM

I am having a problem looping through a large text string passed in to a procedure as nText. I want to be able to break on each delimeter and keep everything previous to the delimiter in the same row. I have tried several different way's but can't seem to get the code to recognize where it needs to break. Any help would be greatly appreciated. I have included two examples I'm trying to work with.

--SAMPLE 1

CREATE PROCEDURE dbo.z_spStringTest3
@string nText

AS

DECLARE @TempTable TABLE (QuestionID Int IDENTITY (1,1) NOT NULL,
                          Question nText)

DECLARE @DataSlice As nVarChar(4000)
DECLARE @counter Int
DECLARE @position Int,
      @MaxLen Int,
      @TextPos Int,
      @Delim Char(1)

SET @Delim = '|'
SET @MaxLen = 4000
SET @TextPos = 0

IF PATINDEX ('%' + @Delim + '%', @String) = 0
BEGIN

   SET @DataSlice = SUBSTRING(@String, @TextPos, @MaxLen)

   INSERT @TempTable (Question) VALUES(REPLACE(@DataSlice, @Delim, ''))

END

IF PATINDEX ('%' + @Delim + '%', @String) > 0
BEGIN
SET @Counter = 1

WHILE @Counter <= DATALENGTH(@String)/2
    BEGIN

     SET @Position = PATINDEX ('%' + @Delim + '%', @String)

     SET @DataSlice = SUBSTRING(@String, @TextPos, @MaxLen)

     SET @TextPos = DATALENGTH(@String)/2 - @Position + @TextPos --@TextPos + @MaxLen + 1 - @Position

     INSERT @TempTable (Question) VALUES(REPLACE(@DataSlice, @Delim, ''))

-- SET @DataSlice = SUBSTRING(@String, @TextPos, @Position) --Remove the previous SQL Statement.

  IF @DataSlice = ''
  BEGIN
       BREAK --Nothing remains to parse so exit the loop.
  END

    SET @counter = @counter + 1
    END
END

SELECT QuestionID, Question
FROM @TempTable
GO

--SAMPLE 2

CREATE Procedure dbo.z_spStringTest2
   @List nText
AS

DECLARE @TempTable TABLE (QuestionID Int IDENTITY (1,1) NOT NULL,
                          Question nText)

DECLARE @DelimPos Int,
   @TextPos Int,
   @DataSlice nVarChar(4000),
   @MaxLen Int,
   @Delim Char(1),
   @StrLength Int

SET @TextPos = 0
SET @Delim = '|'
SET @MaxLen = 4000

-- Grab the total length of the string being passed in
SELECT @StrLength = DATALENGTH(@List) / 2

-- Do this so we can use the replace function to strip out the Delimeter
SELECT @DataSlice = SUBSTRING(@List, @TextPos, @MaxLen)

SET @DelimPos = PATINDEX('%' + @Delim + '%', @List)

-- If the string length is less than the the length to the first delimeter, go ahead and insert to the table.
IF @StrLength <= @MaxLen
   BEGIN
      INSERT @TempTable (Question) VALUES(REPLACE(@DataSlice, @Delim, ''))
   END

-- Else we need to loop through the string and insert to the table.
ELSE

WHILE @StrLength >= @TextPos + PATINDEX('%' + @Delim + '%', @DataSlice)
   BEGIN

-- SELECT @StrLength,@TextPos,@DelimPos

      SELECT @DelimPos = PATINDEX('%' + @Delim + '%', @List)

      SELECT @TextPos = @TextPos + @MaxLen + 1

      INSERT @TempTable (Question) VALUES(REPLACE(@DataSlice, @Delim, ''))

      SELECT @DataSlice = SUBSTRING(@List, @TextPos, @MaxLen)

-- SET @StrLength = @StrLength - @TextPos

   END

SELECT QuestionID, Question
FROM @TempTable

GO

--STRING USED TO EXECUTE PROCEDURE
/*

EXEC dbo.z_spStringTest3
'This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
|This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts|.'

*/

Coop64 · February 16th, 2004, 03:19 PM

I came up with a solution myself. The way this procedure works is, you execute it from another procedure and pass in the text string and the optional delimiter parameters. The proc then loops through looking for the column and row delimiters and inserts the results to a temporary table, SQL would not allow handling the table dynamically. Once the loop is complete the results are returned to the calling procedure. The calling procedure can actually have the results inserted to its own temp table where the data can further be manipulated.

Declare
   @ptList Text,
   @pvColDelim VarChar(100)= null,
   @pvRowDelim VarChar(100) = null

WITH RECOMPILE

AS

-- If the delimiter parameters were not passed in we will set them to Char(1)= Row and Char(2)= Column
SET @pvRowDelim = ISNULL(@pvRowDelim, Char(1))
SET @pvColDelim = ISNULL(@pvColDelim, Char(2))

-- Create the Temp table.
CREATE TABLE #TempTable
(
Col01 varchar(255),
Col02 varchar(255),
Col03 varchar(255),
Col04 varchar(255),
Col05 varchar(255),
Col06 varchar(255),
Col07 varchar(255),
Col08 varchar(255),
Col09 varchar(255),
Col10 varchar(255),
Col11 varchar(255),
Col12 varchar(255),
Col13 varchar(255),
Col14 varchar(255),
Col15 varchar(255),
Col16 varchar(255),
Col17 varchar(255),
Col18 varchar(255),
Col19 varchar(255),
Col20 varchar(255),
Col21 varchar(255),
Col22 varchar(255),
Col23 varchar(255),
Col24 varchar(255),
Col25 varchar(255),
Col26 varchar(255),
Col27 varchar(255),
Col28 varchar(255),
Col29 varchar(255),
Col30 varchar(255),
Col31 varchar(255),
Col32 varchar(255),
Col33 varchar(255),
Col34 varchar(255),
Col35 varchar(255),
Col36 varchar(255),
Col37 varchar(255),
Col38 varchar(255),
Col39 varchar(255),
Col40 varchar(255),
Col41 varchar(255),
Col42 varchar(255),
Col43 varchar(255),
Col44 varchar(255),
Col45 varchar(255),
Col46 varchar(255),
Col47 varchar(255),
Col48 varchar(255),
Col49 varchar(255),
Col50 varchar(255)
)

-- Declare Variables
DECLARE @iTextPos Int
DECLARE @vColStr VarChar(255)
DECLARE @vRowStr VarChar(255)
DECLARE @iColLength Int
DECLARE @iRowEnd Int
DECLARE @vInsertString VarChar(255)
DECLARE @vInsertCols VarChar(255)
DECLARE @vInsertVals VarChar(255)
DECLARE @iRowCounter Int
DECLARE @iColCounter Int
DECLARE @vSQL varchar(255)
DECLARE @iMaxColCounter Int

-- Set variables
SET @iTextPos = 1
SET @iRowCounter = 0
SET @vInsertCols = ''

-- Error checking to make sure parameters were passed in the correct order.
SET @iRowEnd = CHARINDEX(@pvRowDelim, @ptlist, @iTextPos)
SET @iColLength = CHARINDEX(@pvColDelim, @ptlist,@iTextPos)

IF @iRowEnd < @iColLength
BEGIN
   RAISERROR 20000 'spStringToTable: Delimiter Parameters were passed in the wrong order.'
   RETURN(1)
END

-- Reset variables if everything is cool
SET @iRowEnd = 0
SET @iColLength = 0

-- Start the loop while the index is less than the total string length
WHILE @iTextPos < DATALENGTH(@ptlist)
   BEGIN

      SET @iRowCounter = @iRowCounter + 1
      SET @iRowEnd = CHARINDEX(@pvRowDelim, @ptlist, @iTextPos)
      SET @vRowStr = SUBSTRING(@ptlist, @iTextPos, (@iRowEnd - @iTextPos) )+ @pvColDelim

      SET @iColLength = CHARINDEX(@pvColDelim, @vRowStr)
      SET @vInsertVals = ''
      SET @iColCounter = 0

         WHILE @iColLength > 0
            BEGIN
               SET @iColCounter = @iColCounter + 1
               SET @vColStr = SUBSTRING(@vRowStr,1, @iColLength - 1)

               IF @iRowCounter = 1
                BEGIN
                   SET @iMaxColCounter = @iColCounter
                   IF @iColCounter > 1
                      BEGIN
                         SET @vInsertCols = @vInsertCols + ', '
                      END
                      SET @vInsertCols = @vInsertCols + 'Col' + RIGHT( '00' + CAST( @iColCounter AS varchar(20) ), 2 )
                END

               IF @iColCounter > 1
                  BEGIN
                     SET @vInsertVals = @vInsertVals + ', '
                  END

               SET @vInsertVals = @vInsertVals + '''' + @vColStr + ''''
               SET @vRowStr = SUBSTRING(@vRowStr, @iColLength + 1, LEN(@vRowStr))
               SET @iColLength = CHARINDEX(@pvColDelim, @vRowStr)
            END

         -- Increment the index to the end of the next row.
         SET @iTextPos = @iRowEnd + 1

         -- This checks for the possibility of a double Row Delimiter. The blank row is not inserted
         -- and a warning message is returned.
         IF @iMaxColCounter <> @iColCounter
            BEGIN
               RAISERROR ('spStringToTable: One of the rows had a double delimiter, column not inserted',10,2)
            END

         -- If everything looks good then insert column number and data to the temp table.
         ELSE
            BEGIN
               -- Build the INSERT statement.
               SET @vInsertString = 'INSERT INTO #TempTable ( ' + @vInsertCols + ' ) VALUES ( ' + @vInsertVals + ' )'

               -- Execute the insert of the column number and values.
               EXEC sp_sqlexec @vInsertString
            END

   END

-- Select Results back to calling program.
SET @vSQL = 'SELECT ' + @vInsertCols + ' FROM #TempTable'

EXEC sp_sqlexec @vSQL

DROP TABLE #TempTable

GO