mo.notono.us

Monday, August 29, 2005

SQL: Fast String Split Function Using Sequence Table

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

Labels:

1 Comments:

  • 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%).

    By Blogger Oskar, at Tuesday, September 20, 2005 1:00:00 PM  

Post a Comment

Links to this post:

Create a Link

<< Home