T-SQL 101: 43 Concatenating strings in SQL Server

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.

2019-11-11