Split String in SQL Server

Split String in SQL Server.
NVARCHAR and VARCHAR Split in MS SQL.

For all of you there who wishes to preform a simple "split" on a varchar on nvarchar input in ms sql (sql server) this is for you:

Basically, this post's gonna contain a Create Function Script which you will to execute in your server.  It will also contain the simplest way to use it.

There will be more examples in a different post I'll upload soon...

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

    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))
        Set @Cnt = @Cnt + 1
    End
   
    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

Pay attention that the two parameters are mandatory:
1. nvarchar to split.
2. nvarchar which will be the delimiter.

Hope it will help,
Elad Shalom,
CTO at ITweetLive.com