peter.barnet (3/17/2015)
I would have thought that a SQL CLR function would provide the ideal solution here?
It does 🙂
.NET is the right language to process complex types in a RBAR/loop manner; over TSQL's set based.
It is 🙂
It would also be faster I would suspect, though measurements aren't to hand.
Again, it is :-). Several people have published comparisons, including two blogs that were mentioned in the prior post (one by Jeff Moden and another by Aaron Bertrand). Both showing that except for possibly small strings, SQLCLR is far better.
And for anyone who doesn't want to bother with creating the assembly, etc., you can get it already done, and for free, via the SQL#[/url] library (which I am the author of).
It is surprising that anyone even wastes their time doing this in T-SQL anymore. Unless, of course, those who are still stuck on SQL Server 2000 (or earlier). Even Azure SQL Database now supports SQLCLR for Assemblies marked as SAFE.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR