I had a little more fun... again using 17,000+ tokens.

I put the CTE version inside the original function by creating 200 character tokens using the original method (sort of), assuming a single character delimiter, then using CTE to further split them into arrays. It still has a few bugs but it did improve the overall results (hopefully, not due to the bugs):

Original + CTE Hybrid:CPU time = 468 ms, elapsed time = 473 ms.

Original:CPU time = 686 ms, elapsed time = 734 ms.

Tally:CPU time = 172 ms, elapsed time = 157 ms.

CREATE FUNCTION [dbo].[fn_Split_Hybrid]

(

@InputString VARCHAR(max),

@Delimiter char(1)

)

RETURNS @Values TABLE (

VALUE VARCHAR(max)

-- ,IPS VARCHAR(MAX)

--,DCI BIGINT

)

AS

BEGIN

DECLARE @DelimitierLen tinyint = 200

DECLARE @DelimiterCharIndex bigint = @DelimitierLen --BIGINT = CHARINDEX(@Delimiter,@InputString)

WHILE (@DelimiterCharIndex >= @DelimitierLen )

BEGIN

declare @newval varchar(200) = left(@InputString, @DelimitierLen)

SET @InputString = SUBSTRING(@InputString,@DelimitierLen+1, LEN(@InputString)-@DelimitierLen)

SET @DelimiterCharIndex = LEN(@inputString)

--INSERT INTO @Values VALUES (@newval)--, @InputString,@DelimiterCharIndex)

BEGIN

with split(i, token, remainder) as

(select 1

, left(@newval,charindex(@Delimiter,@newval)-1)

, LTRIM(right(@newval,len(@newval)-CHARINDEX(@Delimiter,@newval)))

union all

select i + 1

,case when charindex(@Delimiter,remainder) > 0 then

left(remainder,charindex(@Delimiter,remainder)-1)

else remainder end as token

,LTRIM(right(remainder,len(remainder)-CHARINDEX(@Delimiter,remainder))) as remainder

from split

where charindex(@Delimiter,remainder) >= 0 and token != remainder

)

insert into @Values

Select token

from split

END

END

set @newval = @InputString

BEGIN

with split(i, token, remainder) as

(select 1

, left(@newval,charindex(@Delimiter,@newval)-1)

, LTRIM(right(@newval,len(@newval)-CHARINDEX(@Delimiter,@newval)))

union all

select i + 1

,case when charindex(@Delimiter,remainder) > 0 then

left(remainder,charindex(@Delimiter,remainder)-1)

else remainder end as token

,LTRIM(right(remainder,len(remainder)-CHARINDEX(@Delimiter,remainder))) as remainder

from split

where charindex(@Delimiter,remainder) >= 0 and token != remainder

)

insert into @Values

Select token

from split

END

RETURN

END

GO