Posted By

michanne on 05/05/11


Tagged

StringParsing


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

franksmile0310
wmcleanw


Split a string function


 / Published in: SQL
 

  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
Posted By: iamd3n on January 25, 2013

Nice

Posted By: iamd3n on January 25, 2013

Nice

You need to login to post a comment.