Posted By

robe on 12/05/08


Tagged

sqlserver


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

robe
sulfurito


SQL Server Split function


 / Published in: SQL
 

  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  

You need to login to post a comment.