Return to Snippet

Revision: 16252
at August 2, 2009 19:29 by rengber


Updated Code
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

Revision: 16251
at July 29, 2009 23:37 by rengber


Initial Code
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
  )

Initial URL
http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor

Initial Description
From StackOverflow.  I'm still not quite happy with my understanding of it, but it looks an order of magnitude more elegant than most I've found.

Initial Title
SQL Server String Split Function

Initial Tags
database, sql, data

Initial Language
SQL