Interesting New String Functions

Here are a few new string functions that I cam across while doing some research.  Just goes to show you that you learn something new every day!

STRING_AGG (https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql)

This function is available starting with SQL 2017. It concatenates values from rows and places your separator in between the values. Here I create a comma separated list of databases.

SELECT STRING_AGG(name, ',')
FROM sys.databases 
WHERE name not in ('master','model','msdb','tempdb')
ORDER BY name

STRING_ESCAPE (https://docs.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql)

This function is available starting with SQL 2016 and is currently only able to escape JSON characters. To me it’s not super useful just yet but hopefully they will add more types soon.

SELECT STRING_ESCAPE('SQLRX''s beginning was around	"01/20/2008"', 'json') AS EscapedVal

STRING_SPLIT (https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql)

This function is available starting with SQL 2016. It splits up a string using the separator you provide. In my example, I take the comma separated list of databases from STRING_AGG and separate them back in to a table.

DECLARE @dblist VARCHAR(300) = 'ADMIN,AdventureWorks2016,master,model,msdb,ReportServer,ReportServerTempDB,SQLRX,tempdb,WideWorldImporters,WideWorldImportersDW'

SELECT value AS dblist
FROM STRING_SPLIT(@dblist, ',')

Finally there is no more need to create your own custom functions for these types of common actions!! Yay!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!