Fast(er) Split Function (See related posts)

   1  
   2  --Fast(er) Split function using a sequence table (provided by the UDF dbo.NumberTable)
   3  --Created 08/29/05 by Oskar Austegard from Erland Sommarskog's code at
   4  --http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
   5  ALTER FUNCTION Split (
   6    @List varchar(7998), --The delimited list
   7    @Del char(1) = ',' --The delimiter
   8  ) 
   9  RETURNS @T TABLE (ListID int IDENTITY, Item varchar(7998))
  10  AS
  11  BEGIN
  12    DECLARE @WrappedList varchar(8000)
  13    SELECT @WrappedList = @Del + @List + @Del
  14  
  15    INSERT INTO @T (Item)
  16    SELECT SUBSTRING(@WrappedList, n.Number + 1, CHARINDEX(@Del, @WrappedList, n.Number + 1) - n.Number - 1)
  17    FROM dbo.Numbers n
  18    WHERE n.Number <= LEN(@WrappedList) - 1
  19      AND SUBSTRING(@WrappedList, n.Number, 1) = @Del
  20      AND LEN(SUBSTRING(@WrappedList, n.Number + 1, CHARINDEX(@Del, @WrappedList, n.Number + 1) - n.Number - 1)) > 0
  21    RETURN
  22  END