How does MySQL CONCAT IFNULL work with more than two values?

Question

I need to CONCAT two fields, if they both exist and add parentheses around the second field. Otherwise I just need to list the first field.

Here's the simple version:

SELECT id, CONCAT(name,' (',nickname,')') as name FROM user;

As long as name and nickname both exist you should get results like:

1 | Adam (Alpha Dog)
2 | Bob (Bobby)
3 | Charles (Charlie)

But if there is no nickname it just skips the whole thing as null. For example: id 4, name Doug, nickname null gives the result:

4 | null

What I'd like to see is it to list just the name... like this:

4 | Doug

So I started looking at CONCAT IFNULL. But I'm having a hard time getting it right. Can you help?

Here are a few examples of things I've tried:

SELECT id, CONCAT(IFNULL(name, ' (', nickname, ')', name) as name FROM user;
SELECT id, CONCAT(name, IFNULL(' (', nickname, ')')) as name FROM user;

Answer 1

You could use CONCAT_WS which skips null strings:

SELECT id, CONCAT_WS(' ', name, concat('(',nickname,')')) as name FROM user;

Answer 2

You can concatenate the parens and the nickname, and then use the IFNULL to check to see if the result of that expression is NULL.

If it's null, replace the NULL with an empty string, and then concatenate that expression to the name column.

SELECT id
     , CONCAT(name,IFNULL(CONCAT(' (',nickname,')'),'')) AS name
  FROM user

NOTE: The IFNULL function is shorthand for:

IF(expr1 IS NULL,expr2,expr1)

or the ANSI equivalent:

CASE WHEN expr1 IS NULL THEN expr2 ELSE expr1 END 

There are other approaches that will work just as well. But they all basically need to do the same thing: check if nickname is NULL, and then conditionally include the parens and the nickname, or an empty string.