SQL Server: Split function (or) How to Split string in SQL Server

 SQL Server     sbo     Updated on Jul 23, 2013     VIKRAM     965   

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:

  Related Articles
SQL Server: Variables
SQL Server: Difference between Stored Procedure and Function
SQL Server: Functions
SQL Server: Differences between CHAR, NCHAR, VARCHAR and NVARCHAR
SQL Server: System Databases
SQL Server: Insert values into Identity Column
ASP.Net: List View Basic Example
JQuery: How to replace string in JQuery
Java Script: How to replace string in Java Script
C#: How to replace string in C#
  Recent Articles
SQL Server: Variables
SQL Server: Difference between Stored Procedure and Function
SQL Server: Functions
SQL Server: Differences between CHAR, NCHAR, VARCHAR and NVARCHAR
SQL Server: System Databases
SQL Server: Insert values into Identity Column
ASP.Net: List View Basic Example
JQuery: How to replace string in JQuery
Java Script: How to replace string in Java Script
C#: How to replace string in C#