<< Reading and Writing an encrypted Dataset to Disk | Home | Dave's guide to the EAV >>

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_default, n, 
  CASE SIGN(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n)
   WHEN -1 THEN 
     CASE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n)))
      WHEN 0 THEN DATALENGTH(@Text)-n+1
      ELSE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(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 Data
       , DATALENGTH(@Text)/n  AS Position
, CASE SIGN(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n)
   WHEN -1 THEN 
     CASE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n)))
      WHEN 0 THEN DATALENGTH(@Text)-n+1
      ELSE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(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 n BETWEEN 0 AND (DATALENGTH(@Text) - DATALENGTH(@Separator))
AND ((SUBSTRING(@Text collate database_default,n-DATALENGTH(@Separator),DATALENGTH(@Separator)) = @Separator AND n > 1) OR n = 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
That is NICE!

Good one Dave, works great.

# re: Breaking the 8000 byte limit.

left by Jeff at 12/16/2003 2:25 AM
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
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
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
Fernando, that rocks.

thanks!

# re: Breaking the 8000 byte limit.

left by Azhagupandian at 6/27/2007 9:38 PM
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
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
THank You.
Title  
Name
Email (never displayed)
Url
Comments   
Please add 4 and 6 and type the answer here:
Copyright © byrmol