SQL String concatenation with CONCAT() function

We have been using plus sign (+) operator for concatenating string values for years with its limitations (or more precisely, its standard behaviors). The biggest disadvantage with this operator is, resulting NULL when concatenating with NULLs. This can be overcome by different techniques but it needs to be handled. Have a look on below code;

-- FullName will be NULL for

-- all records that have NULL

-- for MiddleName

SELECT 

    BusinessEntityID

    , FirstName + ' ' + MiddleName + ' ' + LastName AS FullName

FROM Person.Person

-- One way of handling it

SELECT 

    BusinessEntityID

    , FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName AS FullName

FROM Person.Person

-- Another way of handling it

SELECT 

    BusinessEntityID

    , FirstName + ' ' + COALESCE(MiddleName, '') + ' ' + LastName AS FullName

FROM Person.Person

SQL Server 2012 introduced a new function called CONCAT that accepts multiple string values including NULLs. The difference between CONCAT and (+) is, CONCAT substitutes NULLs with empty string, eliminating the need of additional task for handling NULLs. Here is the code.

SELECT 

    BusinessEntityID

    , CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS FullName

FROM Person.Person

If you were unaware, make sure you use CONCAT with next string concatenation for better result. However, remember that CONCAT substitutes NULLs with empty string which is varchar(1), not as varchar(0).