Introduction

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?

Solution

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:

Example


Comparison

Features Eval SQL.NET CLR Regex fn_Split
Support
One Delimiter
Multiple Delimiters
Regular Expression
Remove Duplicate
Order By
Aggregate Values
Other LINQ Methods

Eval SQL.NET extend SQL Language with C# Syntax. You have access to object like Regex class and all LINQ Methods


Performance

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
fn_split TVF 100ms 625ms 5,500ms 55,000ms

Eval SQL.NET

Disclaimer

I'm the library owner.


Pros

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.

Cons

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.

Discussion

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.

Example


CLR Regex Library


Pros

Great scalability, easy to use and slightly faster than Eval SQL.NET.

Cons

The library is limited to Regex. The owner doesn’t look to support this library however you can find some support on Stack Overflow.

Discussion

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!


fn_Split

Pros

Easy to deploy, easy to use and do not require any discussion to get approbation from DBA unlike installing a SQL CLR!

Cons

Way slower than SQL CLR, limited to one delimiter and hundreds of variance of the Table-Valued Function can be found.

Discussion

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

Example


Conclusion

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?