Over a million developers have joined DZone.

Fast(er) Split Function

·
Created 08/29/05 by Oskar Austegard (http://mo.notono.us) from Erland Sommarskog's code at
http://www.sommarskog.se/arrays-in-sql.html#tblnum-core

Requires the presence of a Numbers table - which can be created using the dbo.NumberTable function


--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
Topics:

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}