UNDERCOVER TOOLBOX: fn_SplitString, It’s Like STRING_SPLIT But For Luddites (or those who haven’t moved to SQL 2016 yet).

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.

fn_SplitString Source Code

--SQL Undercover (c)2017
--https://sqlundercover.wordpress.com/
--Written By David Fowler - 1 June 2017
--Table valued function that breaks a delimited string into a table of discrete values

USE master
GO

CREATE FUNCTION fn_SplitString(@DelimitedString VARCHAR(MAX), @Delimiter CHAR(1) = ',')
RETURNS @SplitStrings TABLE (StringElement VARCHAR(255))

AS

BEGIN

WITH Split(XMLSplit)
AS
(SELECT CAST('<element>' + REPLACE(@DelimitedString,@Delimiter,'</element><element>') + '</element>' AS XML))
INSERT INTO @SplitStrings
SELECT p.value('.', 'VARCHAR(255)')
FROM Split
CROSS APPLY XMLSplit.nodes('/element') t(p)

RETURN

END

How To Use fn_SplitString

fn_SplitString (<delimited string>,<delimiter>)

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.