Split a string function


/ Published in: SQL
Save to your folder(s)



Copy this code and paste it in your HTML
  1. CREATE FUNCTION dbo.split_part(@string VARCHAR(MAX),@deliminator VARCHAR(10))
  2. RETURNS TABLE AS
  3. RETURN (
  4. WITH p AS (
  5. SELECT SUBSTRING(@string, 1,
  6. CASE charindex(@deliminator, @string)
  7. WHEN 0
  8. THEN len(@string)
  9. ELSE charindex(@deliminator, @string) - 1
  10. END
  11. ) AS parse_val,
  12. charindex(@deliminator, @string) AS pos
  13. WHERE @string IS NOT NULL
  14. AND len(@string) > 0
  15.  
  16. UNION ALL
  17. SELECT SUBSTRING(@string, pos + 1,
  18. CASE charindex(',', @string, pos + 1)
  19. WHEN 0
  20. THEN len(@string) - pos
  21. ELSE charindex(',', @string, pos + 1) - pos - 1
  22. END ) AS parse_val,
  23. charindex(',', @string, pos + 1) AS pos
  24.  
  25. FROM p
  26. WHERE pos > 0
  27. )
  28. SELECT parse_val FROM p
  29. )
  30. GO

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.