Function that Splits string in SQL Server / Table Valued Function

In this post i will show how to split a String at certain character and shows the OutPut in as a table.
This is also known as Table Valued Function.

OutPut :

Function :

CREATE FUNCTION [dbo].[SplitString]

(

@SplitStr nvarchar(1000),

@SplitChar nvarchar(5)

)

RETURNS @RtnValue table

(

Data nvarchar(50)

)

AS

BEGIN

Declare @Count int

Set @Count = 1

While (Charindex(@SplitChar,@SplitStr)>0)

Begin

Insert Into @RtnValue (Data)

Select

Data = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))

Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))

Set @Count = @Count + 1

End

Insert Into @RtnValue (Data)

Select Data = ltrim(rtrim(@SplitStr))

Return

END

To Execute above Function use the statement as follows

select * from dbo.SplitString(',abc,defg,hij,klm,nopq,Test,123,', ',')