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

mysql concatenation concat ifnull

Ryan · Dec 13, 2012 · Viewed 17.5k times · Source

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

You could use CONCAT_WS which skips null strings:

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