Split String with Comma Separated Delimiter and Convert to Table Rows in SQL Server

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

Check following output to split and convert comma separated string to table in SQL Server