CONCAT Function in SQL Server(TSQL)

I would like to talk to you about a new Built-in Function, CONCAT, that comes with SQL Server 2012.

As you know, before SQL Server 2012, we used a method like the following to combine some values and text in SELECT:

If all fields were text as above, this was not a problem. But when we changed the above code as follows, we were getting a Conversion error because INT and VARCHAR did not match.

I’ve sampled this with direct literal values in this example, but you know we’ll get the same error when we try to merge data from fields of VARCHAR and INT data types.

In a practical way, we can use the CONCAT command as follows to merge column values into a single text.

Thus, you can combine the String and Int fields with the CONCAT function without considering the type of data.

CONCAT also handles very well with NULL values. You don’t need to check with ISNULL or something.

For example, the result returns NULL when you run the following command without using CONCAT.

However, when using the CONCAT function, the result will return as “One, Two, Three, 12345”.