SQL: Concatenating Column Values with Delimiters

Way back in SQL Server 2012, a whole raft of new T-SQL functions were introduced. As I’ve mentioned before, I get excited when there are new T-SQL functions.

Some of the ones added back then, I wasn’t so excited about. EOMONTH was in that category, not because of the functionality, but because of the name (wish it was ENDOFMONTH or better, END_OF_MONTH), and lack of symmetry (lack of a STARTOFMONTH or BEGINNINGOFMONTH).

CONCAT

One that I thought was curious was CONCAT. I remember thinking why on earth do we need a function to concatenate strings. I can already do that. But when I got into using it, I realized how wonderful it was.

The problem with concatenating values is that you first need to convert them to strings. CONCAT does that automatically with all parameters that you pass to it. (It takes a list of values as parameters and you must have at least two parameters). You can see here how I can mix data types in the parameter list:

All the values are implicitly cast to a string. OK, so that’s a little bit useful, but still no big deal?

The really powerful aspect is that it ignores NULL parameters in the list. (The documentation says that it implicitly casts them to an empty string but based on discussions I’ve had with the product group lately, my guess is that it simply ignores any parameter that’s NULL. In the end, the effect is the same).

Now that’s something that’s much messier with normal T-SQL. The problem is that when you concatenate anything that’s NULL with the + operator in T-SQL, the answer is NULL, no matter what the other values are:

But this handles it nicely:

CONCAT_WS

But notice that we’re still not quite there. It’s a pain to need to specify the separator each time (I’ve used N’ ’ as a single unicode space). More of a pain though, is notice that I still have two separators between Tim and Taylor in the example above.

One option that many people seem unaware of, it the CONCAT_WS function that was added (without much fanfare) back in SQL Server 2017. It lets you specify the separator once, and ignores NULLs in the parameter list, and importantly, doesn’t add the separator when the value is NULL.

Ironically, that last part is a bit of a pain if you want to use the function to create a string like a CSV as output (and I wish it had been a parameter to this function) but for this use case, it was perfect.

I’d love to see this expanded to do a better job of creating CSV style output.

2026-08-24