cat playing with string

Working with the SQL Server STRING_AGG Function

Often when generating dynamic sql we must add some separator values in the string(s) we build. A function delivered way back in 2017 called STRING_AGG is a simple way to do this. Let’s look at some examples.

Feature image / license


This function was introduced in SQL Server 2017. It’s purpose is to concatenate values in a string expression and place separators / delimiters between each value.

The Microsoft documentation for this function:

It’s also smart enough not to add a separator at the end of the final string. Additionally, it implicitly converts to strings then concatenates.

When to Use STRING_AGG?

Looking for a use case? Let’s delve into a simple example.

Assume we are crafting some dynamic SQL string containing the names of all the databases on the instance. Perhaps we use this in a cursor to run some commands against multiple databases.

You get something like this – a big ugly string with no separators between each value:

NOTE: it is important to initialize the sql variable before we assign values to it. You can see that if we don’t initialize it some blank or some value that the default is null.

Now let’s add some commas to separate each value:

We get the same string only this time with commas between each value. Note the comma at the end:

Now we trim the trailing comma:

The trimming overwrites the variable to omit the trailing comma:

Next level of improvement is to add our quoted identifiers around each database name – just in case there are spaces:

Finally, we can break out STRING_AGG to bring it home:


As you can see, STRING_AGG makes this much easier and cleaner to read. There’s less chance for mistakes too.

Thanks for reading!

Did you find this helpful? Please subscribe!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

One thought on “Working with the SQL Server STRING_AGG Function

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.