SQL SERVER – CONCAT function and NULL values

CONCAT is the new T-SQL function introduced in SQL Server 2012. It is used to concatenate the values. It accepts many parameter values seperated by comma. All parameter values are concatenated to a single string. But the CONCAT function also handles NULL values in a useful way. Here is how the CONCAT function and NULL values work together.

A simple example is

SELECT CONCAT('SQL Server',' 2012')

which results to SQL Server 2012

The same can be done using + in the earlier versions

SELECT 'SQL Server'+' 2012'

which results to SQL Server 2012

But did you know the advantage of CONCAT function over +?

SELECT 'SQL Server'+' 2012'+NULL

When you execute the above, the result is NULL

But the CONCAT function will simply ignore NULL values

SELECT CONCAT('SQL Server',' 2012',NULL)

The result is SQL Server 2012

So by using CONCAT function, you do not need to worry about handling NULL values.

How many of you know this?

More to Know About the CONCAT Function and NULL Values

CONCAT also saves you from another common error. With the + operator, adding a number to a string fails with a conversion error unless you CAST the number first. CONCAT converts every argument to a string on its own, so CONCAT('Order ', 42) simply returns Order 42. Just remember that it needs at least two arguments, and if every argument is NULL, it returns an empty string, not NULL.

Skipping NULL is not always what you want, though. Think of a full name built from a first name, a space, a middle name, another space and a last name. When the middle name is missing, CONCAT leaves two spaces in a row. On SQL Server 2017 and later, CONCAT_WS(' ', FirstName, MiddleName, LastName) puts the separator only between values that are not NULL, which fixes this neatly.

If you still maintain code for a version older than 2012, wrap each column that can be NULL in ISNULL or COALESCE before you join it with +. It is more typing, but it gives the same safe result.

And if you ever find old code that sets CONCAT_NULL_YIELDS_NULL to OFF to make + behave like CONCAT, do not copy it. That setting is deprecated, and CONCAT is the cleaner way to get the same result. You can check the current value with SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL'), which returns 1 when it is ON.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.