Introduction

STRING_SPLIT Function finally here!

It’s now official, SQL Server 2016 has now a built-in STRING_SPLIT function! DBA and SQL Developer have waited for decade for this features and we finally get it!

We can now already found plenty of article explaining how sweet it this new feature!

But here is the perfect meme which show my great enthusiast toward this method.

STRING SPLIT Meme

Discussion

Don’t worry, I’m very pleased with this new function which will cover 90% of my scenarios but I cannot deny it also, I’m splitting my string using regular expression since SQL Server 2005 and I was expecting a more extensible solution in 2016!


SQL Server 2016 STRING_SPLIT Function

Pros

Built-in function in SQL Server. Do exactly what’s supposed to do, splitting a string with a single delimiter.

Cons

Most limited version you will ever use to split a string with a delimiter. Even the traditional “fn_split” Table-Valued Function is more flexible!

  • Limited to a single character delimiter
  • NO Flexibility (Distinct, Order, Trim)

Discussion

Will I use it? Of course, this is a great addition to SQL Server.

I’m only a little bit upset because I was expecting something better. SQL CLR allow us to split string with regular expression since SQL Server 2005 and we can do it even in SQL Azure. So splitting string with only a single character in SQL Server 2016 and without any option is obviously a deception.

Like I said, this is a very good addition to SQL Server which will cover most of scenario and for all other cases which require a more flexible solution I will just continue to use SQL CLR.

Example


Eval-SQL.NET String Split Function

Pros

Most flexible SQL CLR library ever done. Allow to dynamically evaluate string expression at runtime.

  • Unmatched Flexibility
  • Split with Regular Expression
  • LINQ Syntax (Distinct, OrderBy, Etc.)
  • String Method (Trim, Unsplit, etc.)

Cons

Need to deploy a SQL CLR

Discussion

If you only need to split a string with a single character delimiter, I recommend to use STRING_SPLIT function, there is no need to add some overhead for nothing. Pure SQL Function should always be used over Eval-SQL.NET.

However for all unsupported scenario by the STRING_SPLIT function, this library is without a doubt the ultimate solution to cover them all.

Example


Conclusion

It’s was about time SQL Server has its own STRING_SPLIT function, even is very limited, this cover almost everyone requirements.

Adding a new function doesn’t always require to make an old solution obsolete and this is the perfect example. The STRING_SPLIT function cover most of scenarios and for uncovered one, you can still use a SQL CLR like Eval-SQL.NET library.


Share your experience

  • Are you happy with the new STRING_SPLIT Function?
  • It’s was you was expecting?
  • Which scenario it doesn’t cover?