Paul,

You have aptly demonstrated why you are an MVP and I am but a plebe. 🙂

My mistake, using the same string replicated for the test. I knew I shouldn't - have read that at least a couple of times. But I didn't think to apply it here for some reason (too much holiday spirit I suspect).

New test harness (excuse my crude attempt at generating more unique strings, there's probably a better way) and results.

CREATE TABLE #t1 (MyString VARCHAR(8000))

DECLARE @Pattern VARCHAR(500) = '[0-9]'

;WITH Tally (n) AS (

SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns a CROSS JOIN sys.all_columns b)

INSERT INTO #t1

SELECT REPLICATE(

SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 16)

+ SUBSTRING('00112233445566778899', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 10)

+ REVERSE(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 16))

+ REVERSE(SUBSTRING('00112233445566778899', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 10))

+ SUBSTRING('JUYWXFZHQAMLKNOPIRSTBVDECG', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 16)

+ SUBSTRING('01012323454567678989', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 10)

+ REVERSE(SUBSTRING('JUYWXFZHQAMLKNOPIRSTBVDECG', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 16))

+ REVERSE(SUBSTRING('01012323454567678989', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 10))

+ SUBSTRING('AAABBBCCCDDDEEEFFFGGGHHHII', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 16)

+ SUBSTRING('00011122233344455566677', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 10)

+ REVERSE(SUBSTRING('AAABBBCCCDDDEEEFFFGGGHHHII', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 16))

+ REVERSE(SUBSTRING('00011122233344455566677', 1 + ABS(CHECKSUM(NEWID())) % 10, 1 + ABS(CHECKSUM(NEWID())) % 10))

, 1 + ABS(CHECKSUM(NEWID())) % 75)

--SELECT REPLICATE('abc0123hij456nopq789uvw1y', 1 + ABS(CHECKSUM(NEWID())) % 315)

FROM Tally

--SELECT * FROM #t1

--SELECT MAX(DATALENGTH(MyString)) FROM #t1

DECLARE @MyString VARCHAR(8000), @ItemNumber INT, @Item VARCHAR(8000)

PRINT 'PatternSplitCM'

SET STATISTICS TIME ON

SELECT @MyString=MyString, @ItemNumber=ItemNumber, @Item=Item

--SELECT MyString, ItemNumber, Item, [Matched]

FROM #t1

CROSS APPLY PatternSplitCM(MyString, @Pattern)

WHERE [Matched]=1

SET STATISTICS TIME OFF

PRINT 'SQL#.RegEx_Matches'

SET STATISTICS TIME ON

SELECT @MyString=MyString, @ItemNumber=MatchNum, @Item=Value

--SELECT MyString, MatchNum, Value

FROM #t1

CROSS APPLY SQL#.RegEx_Matches(MyString, N'\d{1,}', 1, NULL);

SET STATISTICS TIME OFF

DROP TABLE #t1

(1000 row(s) affected)

PatternSplitCM

SQL Server Execution Times:

CPU time = 11343 ms, elapsed time = 6576 ms.

SQL#.RegEx_Matches

SQL Server Execution Times:

CPU time = 5975 ms, elapsed time = 6008 ms.

Unfortunately, I don't have SQL 2012 to confirm your results on. Not that they need confirmation given the source.

I find it diabolically clever that the SQL optimizer is able to identify replicated patterns in the strings when constructing the execution plan. Your analysis of that was not only spot on, but extremely easy to comprehend.

Thanks for taking the time to look into this and correct an inappropriate conclusion.

Happy New Year to you sir!