No problem. This relies heavily on the testing framework which Jeff Moden et al set up for his excellent string splitter article[/url].

Here's the rCTE code:

CREATE FUNCTION [dbo].[DS8K_rCTEa](@pString [varchar](8000), @pDelimiter [char](1))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

-- DS8K_rCTEa

WITH x AS (

SELECT

ItemNumber = 1,

pos = 0,

Nextpos = CHARINDEX(',',@pString,1)

UNION ALL

SELECT

ItemNumber = x.ItemNumber + 1,

pos = x.nextpos,

nextpos = ISNULL(NULLIF(CHARINDEX(',',@pString,x.nextpos+1),0),8000)

FROM x

WHERE x.nextpos < 8000 AND x.nextpos+x.pos>0

)

SELECT

ItemNumber,

Item = CASE WHEN pos+nextpos = 0 THEN @pString ELSE SUBSTRING(@pString,pos+1,Nextpos-(pos+1)) END

FROM x

Here's a (hard) tally table version:

ALTER FUNCTION [dbo].[DS8K_Tally1U](@pString [varchar](8000), @pDelimiter [char](1))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

-- DS8K_Tally1U (one-based permanent tally table)

SELECT

ItemNumber= CAST(0 AS BIGINT),

Item= LEFT(@pString,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,1),0)-1,8000))

UNION ALL

SELECT

ItemNumber= ROW_NUMBER() OVER(ORDER BY pos),

Item= SUBSTRING(@pString,pos,[len])

FROM (

SELECT

pos= n+1,

[len]= ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,(n+1)),0)-(n+1),8000)

FROM dbo.Tally1 WITH (NOLOCK)

WHERE n <= ISNULL(DATALENGTH(@pString),0)

AND SUBSTRING(@pString,n,1) = @pDelimiter

) x

Here's the code to test the two functions in sequence:

USE tempdb

GO

SELECT * into JBMTest

FROM (

SELECT 0 as SomeID, NULL as SomeValue UNION ALL --1 NULL

SELECT 1, SPACE(0) UNION ALL --1 b (Empty String)

SELECT 2, SPACE(1) UNION ALL --1 b (1 space)

SELECT 3, SPACE(5) UNION ALL --1 b (5 spaces)

SELECT 4, ',' UNION ALL --2 b b (both are empty strings)

SELECT 5, '55555' UNION ALL --1 E

SELECT 6, ',55555' UNION ALL --2 b E

SELECT 7, ',55555,' UNION ALL --3 b E b

SELECT 8, '55555,' UNION ALL --2 b B

SELECT 9, '55555,1' UNION ALL --2 E E

SELECT 10, '1,55555' UNION ALL --2 E E

SELECT 11, '55555,4444,333,22,1' UNION ALL --5 E E E E E

SELECT 12, '55555,4444,,333,22,1' UNION ALL --6 E E b E E E

SELECT 13, ',55555,4444,,333,22,1,' UNION ALL --8 b E E b E E E b

SELECT 14, ',55555,4444,,,333,22,1,' UNION ALL --9 b E E b b E E E b

SELECT 15, ' 4444,55555 ' UNION ALL --2 E (w/Leading Space) E (w/Trailing Space)

SELECT 16, 'This,is,a,test.' --E E E E

) d

SELECT test.SomeID, test.SomeValue, split.ItemNumber, Item = QUOTENAME(split.Item,'"')

FROM JBMTest test CROSS APPLY dbo.DS8K_rCTEa(test.SomeValue,',') split

SELECT test.SomeID, test.SomeValue, split.ItemNumber, Item = QUOTENAME(split.Item,'"')

FROM JBMTest test CROSS APPLY dbo.DS8K_Tally1U(test.SomeValue,',') split

Try this too, it's fun but not quite legit:

CREATE FUNCTION [dbo].[DS8K_CTE2U](@pString [varchar](8000),@pDelimiter [char](1))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

-- DS8K_CTE2U (zero-based row-constructor tally table)

-- When a CROSS JOIN is used without the ON clause, parentheses can be used to indicate the join order -

-- at least that's the "official" line ;)

SELECT

ItemNumber= CAST(0 AS BIGINT),

Item= CAST(LEFT(@pString, ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, 1),0)-1,8000)) AS VARCHAR(8000))

UNION ALL

SELECT

ItemNumber= ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), -- row_number appears to be necessary

Item= SUBSTRING(@pString,n,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,n),0)-n,8000))

FROM (

SELECT n = n+1

FROM (

SELECT TOP (1+ISNULL(DATALENGTH(@pString),0))

n = (n1 + n2 + n3 + n4)

FROM (VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)

CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1)

CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2)

CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4) --

) n

WHERE SUBSTRING(@pString,n,1) = @pDelimiter

) d

Now, to test the duration of these two queries, your best bet is Jeff's test setup which is an attachment at the end of the article. This query yields a good summary of the results:

SELECT

SplitterName,

Min_ = MIN(Duration),

Max_ = MAX(Duration),

Sum_ = SUM(Duration),

Avg_ = AVG(Duration)

FROM (

SELECT *, Placing = ROW_NUMBER() OVER(PARTITION BY NumberOfElements, MaxElementLength ORDER BY Duration)

FROM dbo.TestResults -- 392 rows

) d

GROUP BY SplitterName

ORDER BY SUM(Duration)