SQL Server Split function


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



Copy this code and paste it in your HTML
  1. --- takes a delimetered value and returns a table
  2. --Usage:
  3. -- SELECT Data
  4. -- FROM dbo.fnSplit('A,B,C', ',');
  5.  
  6. CREATE FUNCTION [dbo].[fnSplit]
  7. (
  8. @RowData VARCHAR(4000),
  9. @SplitOn VARCHAR(5)
  10. )
  11. RETURNS @RtnValue TABLE
  12. (
  13. Id INT IDENTITY(1,1),
  14. DATA VARCHAR(1000)
  15. )
  16. AS
  17. BEGIN
  18. DECLARE @Cnt INT
  19. SET @Cnt = 1
  20.  
  21. While (Charindex(@SplitOn,@RowData)>0)
  22. BEGIN
  23. INSERT INTO @RtnValue (DATA)
  24. SELECT
  25. DATA = ltrim(rtrim(SUBSTRING(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
  26.  
  27. SET @RowData = SUBSTRING(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
  28. SET @Cnt = @Cnt + 1
  29. END
  30.  
  31. INSERT INTO @RtnValue (DATA)
  32. SELECT DATA = ltrim(rtrim(@RowData))
  33.  
  34. RETURN
  35. END

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.