Advanced Search

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