Joining together strings is called concatenating strings. In the first example here you can see SELECT hello, plus NULL, plus there. Now the trick with this, though, is that NULL is a bit of a challenge.
Note that the outcome of that first select statement is NULL is just NULL. As soon as you concatenate something with NULL, you would end up with NULL as the overall answer.
So, in SQL Server 2012 they gave us a CONCAT function. And that allows you to put a list of values (it doesn't matter how many) and concatenate them all. When I first saw this function, I wondered why on earth we needed it, as we could already concatenate strings. However, it does two special things.
The first is that it ignores any NULL values. So you can see in the second SELECT statement above, that it ignore the NULL and just concatenates the other two values.
The second excellent feature of the CONCAT function is that it doesn't care what the data type of your input values is, as it will implicitly convert all the values to strings before concatenating them.
Learning T-SQL
It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.
What is the behavior when you pass a mix of varchar and nvarchar strings? Does it just take the type of the first string in the list (i.e. if you lead with varchar, the returned string will be varchar, and any nvarchar strings will be converted… and vice-versa)?
Hi Paul,
It promotes the return value to nvarchar. It doesn't matter what type the first value is. If any item is nvarchar then the result is nvarchar. (It does the same for things like SQL CLR tupes, etc. as well)