SQL: Concatenating Column Values as Strings in T-SQL (Using CONCAT and CONCAT_WS)

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

Some, I’m not so excited about. EOMONTH was in that category, not because of the functionality, but because of the name (wish it was ENDOFMONTH), and lack of symmetry (lack of a STARTOFMONTH or BEGINNINGOFMONTH).

One that I thought was curious was CONCAT. I thought “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).

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:

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.

CONCAT_WS in SQL Server 2017 comes to the rescue for that. 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. That’s 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’s perfect.

2017-12-18