Always good to see different ways of approaching things. Will have to have a look at the issue with the single item here and then maybe set up some performance tests with the examples given.

I also have been using the tally table version.

CREATE FUNCTION dbo.ProcessStringArray (@inputString NVARCHAR(max), @separator NCHAR(1))

RETURNS @output TABLE(arrayItem NVARCHAR(4000))

AS

BEGIN

/**

* Add start and end separators to the Parameter so we can handle single elements

**/

SET@inputString = @separator + @inputString + @separator

INSERT@output

/**

* Join the Tally table to the string at the character level and when we find a separator

* insert what's between that separator and the next one

**/

SELECTSUBSTRING(@inputString,N+1,CHARINDEX(@separator,@inputString,N+1)-N-1)

FROMdbo.Tally

WHEREN < LEN(@inputString)

ANDSUBSTRING(@inputString,N,1) = @separator

RETURN

END

Where N is the integer column of the tally table.

Pretty certain I found this based on another article here somewhere but cannot find it to give credit.