Posted By

rengber on 07/29/09


Tagged

database sql data tsql String recursion CTE StringParsing


Versions (?)

SQL Server String Split Function


 / Published in: SQL
 

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

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.

  1. CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
  2. RETURNS TABLE
  3. AS
  4. RETURN (
  5. WITH Pieces(pn, start, stop) AS (
  6. SELECT 1, 1, CHARINDEX(@sep, @s)
  7. UNION ALL
  8. SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
  9. FROM Pieces
  10. WHERE stop > 0
  11. )
  12. SELECT pn,
  13. SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
  14. FROM Pieces
  15. )
  16.  
  17.  
  18. --Here's a simpler demo of the CTE Recursion with the string parsing removed for clarity:
  19. --Part before the UNION is called the Anchor expression.
  20.  
  21. begin
  22. WITH Pieces(start, stop) AS (
  23. SELECT 2, 2
  24. UNION ALL
  25. SELECT start + 1, stop + 1
  26. FROM Pieces
  27. WHERE stop < 5
  28. )
  29. SELECT start, stop
  30. FROM Pieces
  31. end
  32.  
  33. --Returns
  34. --start stop
  35. --2 2
  36. --3 3
  37. --4 4
  38. --5 5

Report this snippet  

You need to login to post a comment.