March 10, 2014 08:05 by
PeterWe have been using plus sign (+) operator for concatenating string values for years in SQL Server 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).