Advanced Search

SQL Server Split function

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

Expand | Embed | Plain Text

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.