SQL Server Split Function Example in 2008 to Split Comma Separated String into Table

Introduction: 

Here I will explain simple split function in SQL Server to split comma separated string into table values in SQL Server database or How to split comma separated string with custom split() function in SQL Server. 

Description:

To split comma separated string in SQL Server we need to write custom method for that we need to create one function like as shown below.

Custom Split function to split comma separated string into table

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))

RETURNS @Results TABLE (Items nvarchar(4000))

AS

BEGIN

DECLARE @INDEX INT

DECLARE @SLICE nvarchar(4000)

-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z

--     ERO FIRST TIME IN LOOP

SELECT @INDEX = 1

WHILE @INDEX !=0

BEGIN

-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER

SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)

-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE

IF @INDEX !=0

SELECT @SLICE = LEFT(@STRING,@INDEX - 1)

ELSE

SELECT @SLICE = @STRING

-- PUT THE ITEM INTO THE RESULTS SET

INSERT INTO @Results(Items) VALUES(@SLICE)

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING

SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)

-- BREAK OUT IF WE ARE DONE

IF LEN(@STRING) = 0 BREAK

END

RETURN

END

Once we create custom function Split() run sample query like as shown below

SELECT items FROM [dbo].[Split] ('861,739,10,1670', ',') 

Once we run above query we will get output like as shown below

Output