coalesce vs. concat functions with NULL values

Question

This is my table: enter image description here in my table i should use

SELECT
Title+' '+FirstName+' '+MiddleName+' '+LastName as FullName
from employees
Where BusinessEntityID IN (1,2,4)

But as a result i get only the middle row because that is the only one that does not contain NULL value. The concat() works fine but i was wondering if there is a solution also with coalesce function for this problem.

Can you please help?

Answer 1

On SQL Server 2017 or later, the CONCAT_WS function provides a nice way of doing this:

SELECT
    Title + CONCAT_WS(' ', FirstName, MiddleName, LastName) AS FullName
FROM employees
WHERE BusinessEntityID IN (1, 2, 4);

CONCAT_WS() will ignore null parameters.

Answer 2

So the problem you have is (naive) concatenation of nulls makes the whole string null. The question of whether to just use concat/concat_ws or coalesce/isnull comes down to whether or not you want null values to be interpreted as an empty string, or if you want to substitute a different value in there.

If all you want is an empty string, a variant of concat is the simplest way to go. You would typically want/need to reach for coalesce/isnull if you wanted to replace the value with something other than an empty string (I've given a few examples like if for some reason you want to give everyone a default middle name of x. Ridiculous, I know.). That's not to say you cant do it that way, it's just easier with concat.

Here are a couple different ways you could look at your problem. Please not however, that if your middle name is null, the fact that you're doing ' ' + isnull(Middle, '') + ' ' or you're using concat rather than concat_ws, that means that if your middle name is null, you're going to have two spaces in your string, not one. You'll need to take that into consideration when you handle your nulls.

;with a ( first, Middle, last) as
(
    select 'Janos', 'j', 'Sanchez' union all
    select 'Rob', null, 'walters'
)
select
    First,
    Middle,
    Last,
    NaiveConcat = First + ' ' + Middle + ' ' + Last,
    NaiveConcatWithCoalesce = coalesce(First, '') + ' ' + coalesce(Middle, '') + ' ' + coalesce(Last, ''),
    NaiveConcatWithSubstitution = isnull(First, '') + ' ' + isnull(Middle, 'x') + ' ' + isnull(Last, ''),
    NaiveConcatWithIsnull= isnull(First, '') + ' ' + isnull(Middle, '') + ' ' + isnull(Last, ''),
    TrueConcat = concat(First, ' ', Middle, ' ', Last), -- watch out for double spaces!
    TrueConcatWithSubstitution = concat(First, ' ', isnull(Middle, 'x'), ' ', Last),
    WSConcat = concat_ws(' ', First, Middle, Last)
from a