Thursday, 1 August 2013

Function that Splits string in SQL Server / Table Valued 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,', ',')

No comments:

Post a Comment

Note: only a member of this blog may post a comment.