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!

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!

Follow

About the Author

Lori is an avid runner, cyclist and SQL enthusiast. She has been working for SQLRX for 10 years and has been working with SQL in general for 20 years. Yup...she is an old hand at this stuff.

>