Splitting Strings on SQL Server 2016

A small change, but a great one, in SQL 2016 is native support for splitting strings.

This has to be about the most common user defined function that people write in SQL Server. I’ve certainly seen it across many companies and clients that I’ve worked for over the years. From use in SSRS, or passing multiple values to stored procedures it’s been fairly ubiquitous.

There are countless blog posts about the best way to do this, it’s virtually a competition amongst the best and brightest SQL minds.

From Jeff Moden’s Tally versions:
http://www.sqlservercentral.com/articles/Tally+Table/72993/

To Adam Machanic’s CLR one:
http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

Aaron Bertrand has a great post here that outlines many of these methods and does a performance comparison.
https://sqlperformance.com/2012/07/t-sql-queries/split-strings
(SPOILER : Adam Machanic wins)

With SQL 2016 we have a new way, and apparently it’s even substantially faster that Adam M’s CLR. Figures from Aaron again:
https://sqlperformance.com/2016/03/sql-server-2016/string-split

STRING_SPLIT

This couldn’t be simpler, but I’ll detail it here briefly. The syntax is:
STRING_SPLIT (string, separator)
So in a basic example:
DECLARE @SplitMyString VARCHAR(255) = 'Value1,Value2,Value3,Value4';
SELECT *
FROM STRING_SPLIT(@SplitMyString,',');

And the results:

SplitMyString.png

Easy Peasy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s