in Database

Split function in SQL Server

A while ago, when I was working in a company with my friend Omid Mafakher, he developed a table function which works just link split function in other languages such as VB.NET, C#, C++, etc.

After I get permission from Omid, I decided to publish it as a blog post. Here’s the function:

-- This stored procedure splits a string value with the specified delimiter.
-- Copyright 2007 Omid Mafakher.
Create Function [dbo].[Split] (@String nvarchar(MAX), @Delimiter char(1))
Returns @Results Table (Items nvarchar(MAX))
As
Begin
    Declare @Index int
    Declare @Slice nvarchar(4000) 

    Set @Index = 1
    If @String Is NULL Return 

    While @Index != 0 Begin
        Select @Index = CharIndex(@Delimiter, @String)
        If (@Index != 0) Begin
            Select @Slice = left(@String, @Index - 1)
        End else Begin
            Select @Slice = @String
        End
        Insert into @Results(Items) Values (@Slice)
        Select @String = right(@String, Len(@String) - @Index) 

        If Len(@String) = 0 break
    End
    Return
End

SplitFunction.sql (1.38 kb)