Split Function Example in SQL Server

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

Here I will happy to 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. 


To split comma separated string[abc,xyz,pqr] in SQL Server we need to write custom method for that we need to create one function like as shown below

CREATE FUNCTION dbo.Split(@String nvarchar(2000), @Delimiter char(1))
RETURNS @Results TABLE (value nvarchar(2000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(2000)
--ENTER FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE CHARACTER TO SPLIT STRING
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW SET 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 VALUE INTO THE RESULTS SET
INSERT INTO @Results(value) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE GOT FINAL RESULT
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
 

  Once we create custom function Split() for comma separated string in SQL Server than  run sample query like as shown below 

SELECT items FROM [dbo].[Split] ('ABC,XYZ,PQR', ',')

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

 OUTPUT: