Split String in Sql Server

1. Create a function which return split values in a tabular form

CREATE FUNCTION [dbo].[fnSplitString]

(

    @string NVARCHAR(MAX),

    @delimiter CHAR(1)

)

RETURNS @output TABLE(id int identity(1,1), 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

2. Call function and get result in tabular form

select splitdata from fnSplitString(Dotnet,Programming,',')

This will give result

1 Dotnet
2 Programming