Just Code‎ > ‎

SQL Split function for very large input string

posted Nov 23, 2009, 3:42 AM by Peter Henell
Made by "Fernando", found in a comment on http://weblogs.sqlteam.com/davidm/archive/2003/12/12/655.aspx
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
Comments