[thelist] mssql split (You out there Rudy?)

Anthony Baratta anthony at baratta.com
Wed Feb 28 12:12:21 CST 2007


hmmm...OK - How about this then??
It could be more efficient - I'll leave it to someone else to work that out. 
This should be ported to a stand alone stored procedure.

DECLARE @Delimitedtext varchar(2000);
DECLARE @Delimiter char(1);
SET @Delimitedtext = 'aaa,bbbb,cccc,dddd,';
SET @Delimiter = ',';

DECLARE @SubPart AS varchar(2000)
DECLARE @Remainder AS varchar(2000)
CREATE TABLE #PartsList (Column1 varchar(2000))

SET @SubPart = SUBSTRING(@Delimitedtext,1, CHARINDEX(@Delimiter, @Delimitedtext)-1)
SET @Remainder = SUBSTRING(@Delimitedtext,CHARINDEX(@Delimiter, @Delimitedtext)+1, len(@Delimitedtext))

INSERT INTO #PartsList (COLUMN1) VALUES (@SubPart)

WHILE LEN(@Remainder) > 0
BEGIN

	SET @SubPart = SUBSTRING(@Remainder,1, CHARINDEX(@Delimiter, @Remainder)-1)
	PRINT @SubPart
	SET @Remainder = SUBSTRING(@Remainder,CHARINDEX(@Delimiter, @Remainder)+1, len(@Remainder))
	PRINT @Remainder

	INSERT INTO #PartsList (COLUMN1) VALUES (@SubPart)
END
SELECT * FROM #PartsList
DROP TABLE #PartsList



More information about the thelist mailing list