adam machanic now blogs at http://sqlblog.com

Splitting a string of unlimited length

There are many techniques for splitting a string in T-SQL (in other words, taking a character-delimited string and producing a table of the values), the best of which are encapsulated in Erland Sommarskog's famous article. My favorite of his string splitting techniques is adapted from a previous example that was created by Anith Sen.

Both of these resources are excellent, but sometimes you just need a little bit more. None of their solutions will split a string larger than 8000 characters in length. So I've produced my own modified version. If you're splitting smaller strings then by all means, use theirs! This is certainly slower, but I do believe it's the fastest way in SQL Server 2000 to split a very long string. You will require a numbers table, so make sure you have it on hand...

Anyway, the code:

CREATE FUNCTION dbo.SplitString
(
	@List TEXT,
	@Delimiter CHAR(1)
)
RETURNS @ReturnTbl TABLE (OutParam VARCHAR(20))
WITH SCHEMABINDING
AS
BEGIN
	DECLARE @LeftSplit VARCHAR(7998)
	DECLARE @SplitStart INT SET @SplitStart = 0
	DECLARE @SplitEnd INT
	SET @SplitEnd = 7998

	SELECT @SplitEnd = MAX(Number)
	FROM dbo.Numbers
	WHERE (SUBSTRING(@List, Number, 1) = @Delimiter
			OR Number = DATALENGTH(@List) + 1)
		AND Number BETWEEN @SplitStart AND @SplitEnd

	WHILE @SplitStart < DATALENGTH(@List) - 1
	BEGIN
		SET @LeftSplit = @Delimiter + SUBSTRING(@List, @SplitStart, @SplitEnd - @SplitStart) + @Delimiter

		INSERT @ReturnTbl (OutParam)
		SELECT LTRIM(RTRIM(SUBSTRING(@LeftSplit, Number + 1,
	                    CHARINDEX(@Delimiter, @LeftSplit, Number + 1) - Number - 1)))
	                AS Value
		FROM   dbo.Numbers
		WHERE  Number <= LEN(@LeftSplit) - 1
			AND SUBSTRING(@LeftSplit, Number, 1) = @Delimiter
			AND SUBSTRING(@LeftSplit, Number + 1,
	                    	CHARINDEX(@Delimiter, @LeftSplit, Number + 1) - Number - 1) <> ''

		SET @SplitStart = @SplitEnd + 1
		SET @SplitEnd = @SplitEnd + 7998

		SELECT @SplitEnd = MAX(Number) + @SplitStart
		FROM dbo.Numbers
		WHERE (SUBSTRING(@List, Number + @SplitStart, 1) = @Delimiter
				OR Number+@SplitStart = DATALENGTH(@List) + 1)
			AND Number BETWEEN 1 AND @SplitEnd - @SplitStart
	END

	RETURN
END

This code does nothing more than chunk up the string, using the delimiter as boundries, into small enough pieces (< 8000 characters) that CHARINDEX can be used on them. Then the same algorithm from Erland's split string is applied.

Note that this function does not gracefully handle situations such as passing in the wrong delimiter. So please carefully test your code before deploying this!

Using the function is quite simple:

SELECT * 
FROM dbo.SplitString('something, something else, etc, etc, etc...', ',')

Update, February 15, 2005: Fixed so that only 8000 numbers are needed in the Numbers table for this to work. Previously required as many numbers as were present in the string to be split.