How to split a string in SQL Server?

In this article i will explain how to Split string in SQL Server (or) how to create Split() function in Sql Server.

Execute the following function:

CREATE FUNCTION [dbo].[Split]
(
    @RowData nvarchar(MAX),
    @SplitOn nvarchar(5) 
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(100)
) 
AS
BEGIN 
    Declare @Cnt int

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    End
    
    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

Example 1:

select * from dbo.Split('1,2,3,4,5,6', ',')

Output:

Example 2:

select * from dbo.Split('AAA$BBB$CCC$DDD$EEE$FFF', '$')

Output: