TSQL - CONCAT

Part of my new SQL 2012 TSQL features series is a look at the new CONCAT function.

CONCAT ( string_value1, string_value2 [, string_valueN ] )

From the documents online:

CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned. The implicit conversion to strings follows the existing rules for data type conversions. 

This means it's now possible to easily concatenate strings without worrying about fields being null.  Previously if you used a field that was null when adding strings together it would have resulted in a total null string.  This meant using some custom logic to deal with the null, like the following.

'A' + case when [field] is null then '' else [field] end + 'B'

The new string CONCAT function neatly does away with this as shown below in a very basic example.

From the final result set you can see that including a null in a list of things to concatenate no longer results in a NULL value as it did previously as seen in the second result set.