SplitCSV – Split comma-separated column as Table

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

-- Create dummy tagble
CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
)

INSERT Testdata SELECT 1,  9, '18,20,22'
INSERT Testdata SELECT 2,  8, '17,19'
INSERT Testdata SELECT 3,  7, '13,19,20'
INSERT Testdata SELECT 4,  6, ''
INSERT Testdata SELECT 9, 11, '1,2,3,4'

-- Query
;WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE String > ''
)

SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option