SplitCSV.sql

CREATE FUNCTION [dbo].[SplitCSV]
(@CSV VARCHAR (MAX))
RETURNS  @OutTable TABLE ([ID] VARCHAR (255) NOT NULL)
AS BEGIN

	--hold the current cursor position
	declare @currentposition int

	--hold the next position index of the cursor.
	declare @nextposition    int

	--the length of the string split section
	declare @lengthOfString  int 

	--Assign the starting position of the string
	SET @currentposition = 0 

	--The reason for this is to force entrance into the while loop below.
	SET @nextposition = 1

	WHILE @nextposition > 0
			BEGIN
			-- Assign the next position to be the current index of ‘,’ + 1
			SELECT @nextposition = charindex(',', @CSV, @currentposition + 1)

			-- In here we need to find 2 things. The position of the ‘,’
			-- and the length of the string segment in between.

			SELECT @lengthOfString = CASE 
				WHEN @nextposition > 0 THEN @nextposition - @currentposition
				ELSE len(@CSV) + 1       
			END 

			-- @currentposition - 1 
			--After the length and position is found all we need to do
			--is take the substring of the string passed in.
			IF (
				(substring(@CSV, @currentposition + 1, @lengthOfString-1) <> '') 
				AND (substring(@CSV, @currentposition + 1, @lengthOfString-1) IS NOT NULL)
			) BEGIN 
				INSERT @OutTable (ID) VALUES (substring(@CSV, @currentposition + 1, @lengthOfString-1)) 
			END

			--Set the current position to the next position
			SELECT @currentposition = @nextposition
		END
	RETURN
END
GO