When Microsoft announced STRING_SPLIT, cries of joy and jubilation could be heard all around the offices of SQL Undercover. For those of you who aren’t aware of this miracle of the database world, we’ve finally got a way of converting a delimited string into a table.

If you’ve ever written a proc or script where you needed to take a delimited sting of values and then loop through each value to perform an action then you’ll know how much of a headache it can be and you’ll understand what a lifesaver STRING_SPLIT is. Have a think about that backup script for a second, the one where you pass in a delimited string of database names to backup and somehow need to loop through all the databases and back them up. Wouldn’t it be a wonderful thing if there was an easy way to break that string down into a single columned table and just loop (or dare I say the swear word, cursor – more on that in a later post) through each database, backing it up as you go? With STRING_SPLIT, you can.

That’s great, isn’t it?! Well, yeah as long as you’re running SQL Server 2016 or above and your database is at compatibility level 130 or higher.

But what about those of us who haven’t moved over to 2016 yet or for whatever reason have to run with an older compatibility level? Microsoft’s left you out in the cold but there’s no need to fret, it just so happens that here at SQL Undercover we have an answer for you and that answer is fn_SplitString.

Create the table valued function below in your master database (or other database of choice) and you’ll have a function that’ll mimic the functionality of STRING_SPLIT whilst being compatible with earlier versions of SQL.

Using fn_SplitString is simple, it’s a table valued function that takes in two parameters. The first is a delimited string of values and the second is the delimiter (this should be a single character).

Example

Example 1: Breaking a comma delimited string of characters into a table of discrete values.

Example 2: Want to use something else as a delimiter, a | perhaps? Not problem, just change the delimiter parameter.

So there you go, I hope you find fn_SplitString useful and keep an eye open for other upcoming, Undercover Tool Box articles.

This is part of the Undercover Toolbox, a set of useful scripts, procedures and functions for the discerning DBA.

David Fowler

David is a DBA with over 14 years production experience of SQL Server, from version 6.5 through to 2016. He has worked in a number of different settings and is currently the technical lead at one of the largest software companies in the UK.

Adrian Buckman

After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.T. Adrian has over 4 years of experience working with SQL server and loves all things SQL, Adrian currently works as a Database Administrator for one of the UK’s Largest Software Companies