Post date: Nov 23, 2009 11:42:24 AM
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 ASDECLARE @StrLine VARCHAR(8000)DECLARE @Size BIGINTDECLARE @Start BIGINTDECLARE @Separator VARCHAR(1)DECLARE @TempImport TABLE(Idx BIGINT IDENTITY(1,1), SplitedLine VARCHAR(8000), Size INT)SET @Size = 1SET @Start = 1SET @Separator = ','WHILE (@Start < DATALENGTH(@Text) + 1) BEGINSET @Size = CHARINDEX(@Separator, SUBSTRING(@Text, @Start, DATALENGTH(@Text)), 1)IF @Size = 0 SET @Size = DATALENGTH(@Text) - @Start + 1SET @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 + @SizeENDSELECT * FROM @TempImport