Here's a Split function using XML datatype.

It's preety neat and simple compared to all others that i've seen.

Forget While Loops and recursive CTE's.

Enter XML: 

IF OBJECT_ID('dbo.Split') IS NOT NULL
    DROP FUNCTION dbo.Split
GO
CREATE FUNCTION dbo.Split(@data NVARCHAR(MAX), @delimiter NVARCHAR(5))
RETURNS @t TABLE (data NVARCHAR(max))
AS
BEGIN
<span class="kwrd">DECLARE</span> @textXML XML;
<span class="kwrd">SELECT</span>    @textXML = <span class="kwrd">CAST</span>(<span class="str">&#39;&lt;d&gt;&#39;</span> + REPLACE(@<span class="kwrd">data</span>, @delimiter, <span class="str">&#39;&lt;/d&gt;&lt;d&gt;&#39;</span>) + <span class="str">&#39;&lt;/d&gt;&#39;</span> <span class="kwrd">AS</span> XML);

<span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> @t(<span class="kwrd">data</span>)
<span class="kwrd">SELECT</span>  T.split.<span class="kwrd">value</span>(<span class="str">&#39;.&#39;</span>, <span class="str">&#39;nvarchar(max)&#39;</span>) <span class="kwrd">AS</span> <span class="kwrd">data</span>
<span class="kwrd">FROM</span>    @textXML.nodes(<span class="str">&#39;/d&#39;</span>) T(split)

<span class="kwrd">RETURN</span>

END
GO
DECLARE @text NVARCHAR(max)
SELECT @text = REPLICATE('ab,', 300) + 'ab'
SELECT * FROM dbo.Split(@text, ',')