DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Fast(er) Split Function

01.18.2006
| 6862 views |
  • submit to reddit
        Created 08/29/05 by Oskar Austegard (<a href="http://mo.notono.us">http://mo.notono.us</a>) from Erland Sommarskog's code at
<a href="http://www.sommarskog.se/arrays-in-sql.html#tblnum-core">http://www.sommarskog.se/arrays-in-sql.html#tblnum-core</a>

<b>Requires the presence of a Numbers table - which can be created using the <a href="http://www.bigbold.com/snippets/posts/show/1184">dbo.NumberTable</a> function</b>

--Fast(er) 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
ALTER FUNCTION Split (
  @List varchar(7998), --The delimited list
  @Del char(1) = ',' --The delimiter
) 
RETURNS @T TABLE (ListID int IDENTITY, Item varchar(7998))
AS
BEGIN
  DECLARE @WrappedList varchar(8000)
  SELECT @WrappedList = @Del + @List + @Del

  INSERT INTO @T (Item)
  SELECT SUBSTRING(@WrappedList, n.Number + 1, CHARINDEX(@Del, @WrappedList, n.Number + 1) - n.Number - 1)
  FROM dbo.Numbers n
  WHERE n.Number <= LEN(@WrappedList) - 1
    AND SUBSTRING(@WrappedList, n.Number, 1) = @Del
    AND LEN(SUBSTRING(@WrappedList, n.Number + 1, CHARINDEX(@Del, @WrappedList, n.Number + 1) - n.Number - 1)) > 0
  RETURN
END