Azure SQL Database now has an improved STRING_SPLIT !

Azure SQL Database now has an improved STRING_SPLIT !

I get pretty excited about new T-SQL enhancements. Back in 2016, I was so pleased to see Microsoft finally add a string split option to T-SQL, but my enthusiasm was limited when I saw how it was implemented. Now that’s mostly fixed !

While it’s possible to build functions that did string splitting just like I wanted, the problem is that no matter how you implemented them, they were really just too slow. And most importantly, much slower than a built-in function would be.

The Issues

SQL Server 2016 allowed us to write code like this:

SELECT [value] 
FROM STRING_SPLIT('Hello,there,Greg', ',') AS s;

That code would return this output:

STRING_SPLIT is a table-valued function. It took two parameters:

  • The string to be split (Unicode or not)
  • The separator

It returned a set of rows with a single column called value, and it was fast.

So what were the problems?

  • The separator could only be a single character
  • Often you need to trim the values being returned. (That can obviously be done outside this function using TRIM but it’d be nice if there was an option to do it in the function).
  • But the biggest issue of all was that because a table-valued function doesn’t have an inbuilt order for returning rows, you didn’t know which token was which, that had been extracted from the string.

Now a TVF returns a table, and tables don’t have built-in orders either. Nor do views. But what was needed was another column that told you which rows was which, and you could then use that for ordering when needed.

Azure SQL Database

STRING_SPLIT in Azure SQL Database now has an optional parameter to enable an ordinal (i.e. a position).

That extra parameter is a bit or an int that says if an ordinal value should be added to the output.

So this code:

SELECT [value], ordinal 
FROM STRING_SPLIT('Hello,there,Greg', ',', 1) AS s;

Now returns:

I love working with Azure SQL Database for many reasons, but one of those is that you get new language extensions before other parts of SQL Server. I’ve been using this feature since the day that I first heard it existed, and it’s working great. Now the updated documentation is online as well.

It also means that I can obtain any specific token that I want. For example, get me the third token in the string:

SELECT [value] 
FROM STRING_SPLIT('Hello,there,Greg', ',', 1) AS s
WHERE ordinal = 3;

And that returns:

This is a much-needed enhancement.

2021-11-05