Here's my latest splitter. I'm still testing it to be included in the rewrite of the "Tally Table" article but it blows the doors off the old splitter especially when you get over 1,000 bytes. I haven't YET tested it for any of the MAX datatypes but I will tell you this... as soon as you change from (say) VARCHAR(8000) to VARCHAR(MAX), most splitter code that uses a join runs twice as slow. That's why I usually maintain two splitters... 1 for "normal" and 1 for "MAX" datatypes.

CREATE FUNCTION dbo.DelimitedSplit8KNEW

--===== Created by Jeff Moden (Prototype: Testing Still in Progress)

--===== Define I/O parameters

(

@pString VARCHAR(8000),

@pDelimiter CHAR(1)

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

WITH

E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

cteTally(N) AS (

SELECT 0 UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4

)

SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY t.N),

ItemValue = SUBSTRING(@pString,t.N+1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,t.N+1),0),DATALENGTH(@pString)+1)-t.N-1)

FROM cteTally t

WHERE t.N BETWEEN 0 AND DATALENGTH(@pString)

AND (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)

;

GO