Breaking the 8000 byte limit.

Here is an extension to the CSV to Row function that breaks the 8000 byte varchar limit.

It is only limited to the size of your "Numbers" table (I think)

CREATE FUNCTION RowParser
(
@Text TEXT,
@Separator VARCHAR(3)  ','
)
RETURNS TABLE
AS
RETURN
(
SELECT n,
  
SUBSTRING(@Text collate database_defaultn
  
CASE SIGN(CHARINDEX(@Separator collate database_default@Text collate database_default,n)-n)
   
WHEN -THEN 
     
CASE PATINDEX('%,%'SUBSTRING(@Text collate database_defaultnABS(CHARINDEX(@Separator collate database_default@Text collate database_default,n)-n)))
      
WHEN THEN DATALENGTH(@Text)-n+1
      
ELSE PATINDEX('%,%'SUBSTRING(@Text collate database_defaultnABS(CHARINDEX(@Separator collate database_default@Text collate database_default,n)-n))) -1
     
END
   ELSE 
ABS(CHARINDEX(@Separator collate database_default@Text collate database_default,n)-n)
   
ENDAS Data
       
DATALENGTH(@Text)/n  AS Position
CASE SIGN(CHARINDEX(@Separator collate database_default@Text collate database_default,n)-n)
   
WHEN -THEN 
     
CASE PATINDEX('%,%'SUBSTRING(@Text collate database_defaultnABS(CHARINDEX(@Separator collate database_default@Text collate database_default,n)-n)))
      
WHEN THEN DATALENGTH(@Text)-n+1
      
ELSE PATINDEX('%,%'SUBSTRING(@Text collate database_defaultnABS(CHARINDEX(@Separator collate database_default@Text collate database_default,n)-n))) -1
     
END
   ELSE 
ABS(CHARINDEX(@Separator collate database_default@Text collate database_default,n)-n)
   
END AS NumberStep
FROM Numbers
WHERE BETWEEN AND (DATALENGTH(@Text) - DATALENGTH(@Separator))
AND ((
SUBSTRING(@Text collate database_default,n-DATALENGTH(@Separator),DATALENGTH(@Separator)) @Separator AND 1) OR 1)
)
GO

I originally had an example using 10550 byte text string but it takes up to much real estate.
Any problems, optimisations or insults please tell.

Print | posted on Friday, December 12, 2003 11:08 PM

Feedback

# re: Breaking the 8000 byte limit.

left by Damian at 12/12/2003 11:49 PM Gravatar
That is NICE!

Good one Dave, works great.

# re: Breaking the 8000 byte limit.

left by Jeff at 12/16/2003 2:25 AM Gravatar
Nice David.

I still wonder, though: wouldn't it be faster just to loop in this case? why bring a numbers table into the equation ... yes, for multiple rows a "setbased" technique is the way the go, but for 1 single string stored in memory, it seems like it would be faster to loop through it the traditional way rather than joing to a table in your database.

Maybe I'll try to do some comparisons later on today if I get a chance.

# 8,001 Bytes And Beyond (SQL Arrays)

left by Pseudo-Random at 3/9/2006 9:36 PM Gravatar
I recently sat down to try to figure the best way to pass a comma-delimited array to a SQL Server 2000...

# re: Breaking the 8000 byte limit.

left by Fernando at 3/23/2007 7:02 AM Gravatar
Heres my code for Import TEXT to TABLE....
I made on My work, to import an text file for mssql 2000 !!! =)


CREATE PROCEDURE dbo.EachLineToTable --'String1,String2,String3,String4,MyNameIs,TestDriveFromBIGString,GOGOGO,Bye'
@Text TEXT
AS
DECLARE @StrLine VARCHAR(8000)
DECLARE @Size BIGINT
DECLARE @Start BIGINT
DECLARE @Separator VARCHAR(1)
DECLARE @TempImport TABLE(Idx BIGINT IDENTITY(1,1), SplitedLine VARCHAR(8000), Size INT)

SET @Size = 1
SET @Start = 1
SET @Separator = ','

WHILE (@Start < DATALENGTH(@Text) + 1) BEGIN
SET @Size = CHARINDEX(@Separator, SUBSTRING(@Text, @Start, DATALENGTH(@Text)), 1)
IF @Size = 0 SET @Size = DATALENGTH(@Text) - @Start + 1
SET @StrLine = SUBSTRING(SUBSTRING(@Text, @Start, DATALENGTH(@Text)), 1, @Size)
SET @StrLine = REPLACE(@StrLine,@Separator,'')
INSERT INTO @TempImport(SplitedLine, Size) VALUES(@StrLine, LEN(@StrLine))
SET @Start = @Start + @Size
END

SELECT * FROM @TempImport

# re: Breaking the 8000 byte limit.

left by skichick at 5/12/2007 12:41 AM Gravatar
Fernando, that rocks.

thanks!

# re: Breaking the 8000 byte limit.

left by Azhagupandian at 6/27/2007 9:38 PM Gravatar
It is really amazing.. Thanks a lot

# re: Breaking the 8000 byte limit.

left by DANIEL FROM CHILE at 8/4/2008 2:59 PM Gravatar
I OWN MY LIFE TO YOU XD FERNANDO

# re: Breaking the 8000 byte limit.

left by rüya tabiri at 9/8/2008 2:28 AM Gravatar
THank You.

# re: Breaking the 8000 byte limit.

left by John Bellew at 2/5/2009 11:58 PM Gravatar
Fernando... you rock! amazing stuff.

# re: Breaking the 8000 byte limit.

left by Ankit at 4/8/2009 5:35 PM Gravatar
Hey dave , how do you call this function

# re: Breaking the 8000 byte limit.

left by that is to thank you at 7/26/2009 7:31 PM Gravatar
i have been searching for your function
and finally i have found it
thnx 4 ur Effor ;)

# re: Breaking the 8000 byte limit.

left by Coder at 3/19/2010 12:56 AM Gravatar
Thanks Farnendo

# re: Breaking the 8000 byte limit.

left by TC at 10/7/2010 4:03 AM Gravatar
Top routine

# re: Breaking the 8000 byte limit.

left by SQLQUEEN at 4/25/2011 1:09 AM Gravatar
Where is the row table ?
weblogs.sqlteam.com/.../655.aspx
Comments have been closed on this topic.