How to Spilt string sql server ?

In this post I wrote a SQL function to split string in sql server. In most of cases we need to split sql string.

Creating sql function:-

CREATE FUNCTION [dbo].[fnSplitSqlString]

(

    @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

Calling above function :-

Select * from [dbo].fnSplitSqlString('SQL$ASP.NET$WCF$JQuery$c#.NET','$')

Output:-

I hope you enjoyed this post. Please comments your feedback and questions.