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.
Legacy Comments
Damian
2003-12-12 |
re: Breaking the 8000 byte limit. That is NICE! Good one Dave, works great. |
Jeff
2003-12-16 |
re: Breaking the 8000 byte limit. 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. |
Fernando
2007-03-23 |
re: Breaking the 8000 byte limit. 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 |
skichick
2007-05-12 |
re: Breaking the 8000 byte limit. Fernando, that rocks. thanks! |
Azhagupandian
2007-06-27 |
re: Breaking the 8000 byte limit. It is really amazing.. Thanks a lot |
DANIEL FROM CHILE
2008-08-04 |
re: Breaking the 8000 byte limit. I OWN MY LIFE TO YOU XD FERNANDO |
rüya tabiri
2008-09-08 |
re: Breaking the 8000 byte limit. THank You. |
John Bellew
2009-02-05 |
re: Breaking the 8000 byte limit. Fernando... you rock! amazing stuff. |
Ankit
2009-04-08 |
re: Breaking the 8000 byte limit. Hey dave , how do you call this function |
that is to thank you
2009-07-26 |
re: Breaking the 8000 byte limit. i have been searching for your function and finally i have found it thnx 4 ur Effor ;) |
Coder
2010-03-19 |
re: Breaking the 8000 byte limit. Thanks Farnendo |
TC
2010-10-07 |
re: Breaking the 8000 byte limit. Top routine |
SQLQUEEN
2011-04-25 |
re: Breaking the 8000 byte limit. Where is the row table ? weblogs.sqlteam.com/.../655.aspx |