SQL Server String Split Function

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS TABLE
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
 
 
--Here's a simpler demo of the CTE Recursion with the string parsing removed for clarity: 
--Part before the UNION is called the Anchor expression.  
 
begin
   WITH Pieces(start, stop) AS (
      SELECT 2, 2
      UNION ALL
      SELECT start + 1, stop + 1
      FROM Pieces
      WHERE stop < 5
    )
    SELECT start, stop
    FROM Pieces
 end
 
--Returns
--start stop
--2	2
--3	3
--4	4
--5	5