|
Subject:
|
Parsing Large Delimited Text String
|
|
Posted By:
|
Coop64
|
Post Date:
|
1/22/2004 10:31:58 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|.'
*/
|
|
Reply By:
|
Coop64
|
Reply Date:
|
2/16/2004 2:19:40 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
|
|
