Table Valued Split Function

During the initial phase of development of our project one of my colleague stumbled upon a table-valued Split function (from the net ofcource J )
I thoroughly enjoyed using this Split function throughout my SPs and functions.
Dynamic Query cannot be used in Functions and I’d a requirement to write a table valued function which has to parse a variable number of Items.
I got around the problem by using the Split function in it.
This is what I did:
Send the list of ItemIds as a comma separated string
Parse it using the Split function inside the Table Valued function.

The Split Function code goes like this:

CREATE FUNCTION [dbo].[Split](@List nvarchar(2000), @SplitOn
nvarchar(5))
RETURNS @RtnValue TABLE (Id INT
IDENTITY(1,1), Value NVARCHAR(100))
AS
BEGIN
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue
(value)
SELECT Value =
LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1)));

SET @List =
SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List));
END
INSERT INTO @RtnValue (Value) SELECT Value =
LTRIM(RTRIM(@List));

RETURN;
END

Courtesy: to the unknown Internet site that hosted it and to the unknown developer who created it.

Agreed that this function has limitation like you cannot parse a very big string since the Value column’s limit is 100.
But still this Split function is very useful!!