Split function

Split function:
Split function is a user defined function it accept a string containing a stream of words separated by “,” and display it as individual rows in a table.

Code:
CREATE FUNCTION splitFunction
(@List nvarchar(2000))
RETURNS
 @RtnValue table ( Value nvarchar(100))
AS
BEGIN
While (Charindex(',',@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(',',@List)-1)))
Set
@List = Substring(@List,Charindex(',',@List)+len(','),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END

Executing the following query
SELECT *
FROM dbo.splitFunction('Sara,Gopi,Rishma,Madhan,Anbu')

The output is:

Posted in: SQL Server Tutorial