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!