Advanced Search

SQL Server String Split Function

/ Published in: SQL
Save to your folder(s)
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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.