SPLIT function in SQL (split string on the basis of delimiter passed)

Sometimes it is needed to Split string on the basis of delimiter passed, in such case below function can be used,

-- Select * from   [fn_SplitString]  ('1,1;25,2;',';')        

-- Split string by deimiter passed, here deimiter is ‘;’. =============================================================================   

CREATE FUNCTION [dbo].[fn_SplitString]

(

    @string NVARCHAR(MAX),

    @delimiter CHAR(1)

)

RETURNS @output TABLE(SplitData NVARCHAR(MAX)

)

BEGIN

    DECLARE @start INT, @end INT

    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

    WHILE @start < LEN(@string) + 1 BEGIN

        IF @end = 0 

            SET @end = LEN(@string) + 1

      

        INSERT INTO @output (SplitData) 

        VALUES(SUBSTRING(@string, @start, @end - @start))

        SET @start = @end + 1

        SET @end = CHARINDEX(@delimiter, @string, @start)

       

    END

    RETURN

END

OutPut

SplitData

1,1

25,2