Interesting New String Functions

By Lori Brown | SQL Development

Apr 19

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!


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.

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


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


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 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!


About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.