Jeff Moden (5/8/2012)
borisk 57245 (5/8/2012)
RegEx is Thee best tool in order to find any pattern within given string[did couple of test including .Net String manipulation and limited RegEx in SQL , Like/PATIndex....], and it proves to be much faster than any other way.Cool! Prove it. Show us the test code you used.
Check Attachment ...
Input: 4000 Chars, A with delimmited COMMA
Test: tokenizer split with SQLCLR[RegEx.Matches, String.Split], and TSQL split
Run time - String.Split
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Run time - RegEx.Matches
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
Run time - TSQL Tokenizer
SQL Server Execution Times:
CPU time = 577 ms, elapsed time = 589 ms.
nothing to add basically 🙂
as for now, could not find any possible reason not to use SQL CLR RegEx or other CLR function other than readability, PATTINDEX = LIKE are much less efficient both in performance and versatility...