0

I copied this code from another thread here (T-SQL: Opposite to string concatenation - how to split string into multiple records) and it works great for what I needed it to do but if asked, I couldn't explain exactly how it does what it accomplishes...can someone explain what the Recursive CTE is doing step by step please?

 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
Community
  • 1
  • 1
Jason R.
  • 379
  • 1
  • 6
  • 19
  • A handy trick for understanding and debugging CTEs is to look at the intermediate results. Try changing the `SELECT` statement at the end to `SELECT * FROM Pieces`. – HABO Apr 16 '15 at 18:00

1 Answers1

0

Here are some articles to give you a good starting point with recursion. Essentially the first query in Pieces is an anchor and the second query after the union creates the recursion (notice how it calls Pieces inside the definition of Pieces)

SQL Server CTE Basics - Simple Talk

Technet

Dave Pinal Article

Matt
  • 1,441
  • 1
  • 15
  • 29