I agree with Slawomir Mucha. Also, if the occurrence number provided exceeds the number of substrings, the last substring is returned. I prefer to return an empty string in those cases. Here is a function I use, which also allows for longer delimiters:

CREATE FUNCTION dbo.fGetToken

(

  @parm varchar(8000),

  @delim varchar(100),

  @whichOccur smallint

)

RETURNS varchar(8000)

AS

BEGIN

DECLARE @occur int, @spos int, @startPos int, @maxlen int

DECLARE @token varchar(8000)

DECLARE @delimLen int

-- Len() function ignores trailing blanks, so if our delimiter is a space,

-- or multiple spaces, Len() will returns zero.

IF Len(@delim) = 0 AND DataLength(@delim) > 0

  SET @delimLen = DataLength(@delim)  --  DataLength(@delim) * 2 for nvarchar

ELSE

  SET @delimLen = Len(@delim)

SET @occur = 0

SET @startPos = 1

SET @spos = 1 - @delimLen

SET @maxLen = Len(@parm)

WHILE @occur < @whichOccur AND @parm <> '' AND @parm IS NOT NULL AND @startPos < @maxLen

BEGIN

  SET @occur = @occur + 1

  SET @startPos = @spos + @delimLen

  SET @spos = CHARINDEX( @delim , @parm, @startPos )

  IF @spos = 0

    BREAK

END

IF @occur <> @whichOccur

  SET @token = '' -- or NULL

ELSE

  IF @spos = 0

    SET @token = Substring(@parm, @startPos, 8000)

  ELSE

    SET @token = SubString( @parm, @startPos, @spos - @startPos)

RETURN @token

END