The only problem with this function is that it really isn't any faster than the function I had been using since 2001. That's because of the dbo.NumberTable function used within. If that is replaced by a lookup in a static Numbers table, the function speeds up by a lot (~300%).
Tuesday, September 20, 2005 1:00:00 PM
Erland Somarskog has some excellent articles on a few interesting SQL topics. One is titled Arrays and Lists in SQL Server, in which he covers various ways of parsing delimited strings into tables. The following is a minor modification of one of the functions he describes; I changed it from an inline function to a table function to allow setting the max value for my NumberTable function. This also allows me to create the @WrappedList parameter which simplifies the logic some:
--Fast Split function using a sequence table (provided by the UDF dbo.NumberTable)
--Created 08/29/05 by Oskar Austegard from Erland Sommarskog's code at
--http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
CREATE FUNCTION Split (
@List varchar(7998), --The delimited list
@Del char(1) = ',' --The delimiter
)
RETURNS @T TABLE (Item varchar(7998))
AS
BEGIN
DECLARE @WrappedList varchar(8000), @MaxItems int
SELECT @WrappedList = @Del + @List + @Del, @MaxItems = LEN(@List)
INSERT INTO @T (Item)
SELECT SUBSTRING(@WrappedList, Number + 1, CHARINDEX(@Del, @WrappedList, Number + 1) - Number - 1)
FROM dbo.NumberTable(1, @MaxItems) n
WHERE n.Number <= LEN(@WrappedList) - 1
AND SUBSTRING(@WrappedList, n.Number, 1) = @Del
RETURN
END
posted by Oskar Austegard at 1:32 PM on Aug 29, 2005
"SQL: Fast String Split Function Using Sequence Table"
1 Comment -
The only problem with this function is that it really isn't any faster than the function I had been using since 2001. That's because of the dbo.NumberTable function used within. If that is replaced by a lookup in a static Numbers table, the function speeds up by a lot (~300%).
Tuesday, September 20, 2005 1:00:00 PM