We've found good use of the following tally table and UDF. It handles empty elements and works fast for us. We've done large and small sets against it.

CREATE TABLE dbo.Tally

(N INT)

-- Default Data

-- Taken from the following website:

-- http://www.sqlservercentral.com/articles/TSQL/62867/

--=============================================================================

-- Create and populate a Tally table

--=============================================================================

--===== Conditionally drop

IF OBJECT_ID('dbo.Tally') IS NOT NULL

DROP TABLE dbo.Tally

--===== Create and populate the Tally table on the fly

SELECT TOP 3000000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Let the public use it

GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

GO

CREATE FUNCTION udf_StrList2Table (

@List NVARCHAR(MAX) = N'',

@Delimiter NCHAR(1) = N','

)

RETURNS TABLE

AS

RETURN

SELECT SUBSTRING( @Delimiter + @List + @Delimiter, n + 1,

CHARINDEX( @Delimiter, @Delimiter + @List + @Delimiter, n + 1 ) - n - 1 ) AS Value,

ROW_NUMBER() OVER ( ORDER BY n ) AS ListPos

FROM Tally

WHERE SUBSTRING( @Delimiter + @List + @Delimiter, n, 1 ) = @Delimiter

AND n < CAST(LEN( @Delimiter + @List + @Delimiter ) as int)