SPLIT FUNCTION IN SQL SERVER

Split Function is a user defined table valued function which accepts two parameters, one is string to split and other one is delimiter to split the first string.

CREATE FUNCTION [Split]
( 
         @String nvarchar(MAX), 
         @Delimiter char(1)
)
RETURNS @Results TABLE
( 
         RowNo INT , 
         Items varchar(4000)
)
AS
BEGIN 
         DECLARE @INDEX INT 
         DECLARE @RowNo INT 
         DECLARE @SLICE nvarchar(4000)

         SELECT @INDEX = 1 
         SELECT @RowNo=0

         IF @String IS NULL RETURN 

         WHILE @INDEX !=0 
         BEGIN 
                     SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) 

                     IF @INDEX !=0 
                                 SELECT @SLICE = LEFT(@STRING,@INDEX - 1) 
                     ELSE 
                                 SELECT @SLICE = @STRING 

                     SELECT @RowNo=@ROWNO+1 

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

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

         RETURN
END