One of the most common problem in SQL is splitting a string with a delimiter. Sometime the problem become even harder when you need to remove duplicate, sort by alphabetical order, aggregate values, filter values, etc.
So what are the available options over SQL Server 2016 STRING_SPLIT function?
They exists two techniques for spliting a string in SQL Server. The first technique is creating a Table-Valued Function which is normally named fn_Split and the second is using a SQL CLR.
In this article, we will compare the following libraries/script:
|Library||1,000 Rows||10,000 Rows||100,000 Rows||1,000,000 Rows|
|Eval SQL.NET||4 ms||13 ms||160 ms||1,650 ms|
|CLR Regex Library||4 ms||12 ms||155 ms||1,600 ms|
I'm the library owner.
Unmatched flexibility, great scalability and outstanding support. In term of flexibility, nothing exists yet come close to what this library can achieve. Not only you can use regular expression delimiter but also use LINQ Methods to remove duplicate, sort by, filter values and more.
The library doesn't only bring Regex functionality but a new world of possibilities and this can be also view as a negative point. Some sql developer may overuse this library instead of using pure SQL and forget the simple rule: Use the right tool for the right job.
The library is free up to 50 characters which is more than enough for all Regex methods. But if you really want to unleash all the power of this library and use more characters, a PRO License is required.
The library use the same compiler as the Eval Expression.NET.
CLR Regex Library
Great scalability, easy to use and slightly faster than Eval SQL.NET.
The library is limited to Regex. The owner doesn’t look to support this library however you can find some support on Stack Overflow.
Unless you want to limit what SQL your developer can do, there is no point to use this library which can only do Regex over Eval SQL.NET which can do almost everything C# allow to do!
Easy to deploy, easy to use and do not require any discussion to get approbation from DBA unlike installing a SQL CLR!
Way slower than SQL CLR, limited to one delimiter and hundreds of variance of the Table-Valued Function can be found.
Hundreds of version of this library exists, some in fact allow multiple delimiters, some allow to perform distinct but most of time, you end with a coding horror function like the one in this post which can been done in 1-2 lines using Eval SQL.NET like my answer
If your only requirement is to split string with a delimiter and performance is not a requirement, the fn_Split TVF is the best choice.
However, if you are looking for flexibility and scalability, Eval SQL.NET is without a doubt the library you should explore and the only one which can handle easily all complex scenarios. In the part II and III of this article (both available soon), you will learn how to use this library with LINQ methods and a lot of examples will be provided.
Share your experience
- What's features is missing?
- Which library are you using and why?
- What's your performance experience?