Splitting a string of unlimited length

Adam Machanic — Thu, 13 Jul 2006 01:20:00 GMT

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 = 7997

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

	WHILE @SplitStart < @SplitEnd
	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 REPLACE(SUBSTRING(@LeftSplit, Number, 1), ' ', CHAR(255)) = 
				REPLACE(@Delimiter, ' ', CHAR(255))
			AND '' <>
				SUBSTRING
				(
					@LeftSplit, 
					Number + 1, 
					CHARINDEX(@Delimiter, @LeftSplit, Number + 1) - Number - 1
				)

		SET @SplitStart = @SplitEnd + 1
		SET @SplitEnd = @SplitEnd + 7997

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

	RETURN
END
GO

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.

Update, February 23, 2009: Four years since the last update and a new bug is discovered by the insightful Craig Hathaway! Fixed the function so that it now handles space as a delimiter. Thanks, Craig!

Adam Machanic : splitting strings

Splitting a string of unlimited length