What is String to Split in SQL SERVER PART 7 With Example Code

The function takes two parameters: @String which is the string to split, and @Delimiter which is the character to split on.It returns a table variable @Result with a single column Items containing the split items.Inside the function, it initializes some variables and checks if the input string is null or empty. If so, it returns immediately.It then enters a loop to split the string.

CREATE FUNCTION [dbo].[Split] (
    @String NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Result TABLE (Items NVARCHAR(4000))
AS
BEGIN
    DECLARE @INDEX INT;
    DECLARE @SLICE NVARCHAR(4000);
 
    -- INITIALIZE THE INDEX TO START AT 1
    SELECT @INDEX = 1;
 
    -- IF THE INPUT STRING IS NULL, THEN RETURN
    -- IF THE INDEX IS 0, THEN THE STRING IS EMPTY
    IF LEN(@String) < 1
        OR @String IS NULL
        RETURN;
 
    WHILE @INDEX != 0
    BEGIN
        -- GET THE INDEX OF THE FIRST OCCURRENCE OF THE SPLIT CHARACTER
        SELECT @INDEX = CHARINDEX(@Delimiter, @String);
 
        -- IF THE INDEX IS 0, THEN THE STRING IS EMPTY
        IF @INDEX != 0
            SELECT @SLICE = LEFT(@String, @INDEX - 1);
        ELSE
            SELECT @SLICE = @String;
 
        -- INSERT THE ITEM INTO THE RESULT SET
        INSERT INTO @Result(Items) VALUES (@SLICE);
 
        -- REMOVE THE ITEM FROM THE STRING
        SELECT @String = RIGHT(@String, LEN(@String) - @INDEX);
 
        -- IF THE STRING IS EMPTY, BREAK THE LOOP
        IF LEN(@String) = 0 BREAK;
    END
 
    RETURN;
END;

 EXEC SP_Script_And_Model_Script @ModuleName = 'FreeAndCourseModel', @Tables = 'Student,Course';