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;

Tache

IFNULL( CONCAT( '(', nickname, ')' ), '' ) – Eggett

Something to point out that threw me off at first. When nickname is null then CONCAT('(',nickname,')') will also return null. – Darelldarelle

Answer 1

You could use CONCAT_WS which skips null strings:

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

Naxos

if nickname is NULL you will get NULL as result – Eggett

If you'd need to use CONCAT instead of CONCAT_WS for any reason, you can do a COALESCE(column, '') wrapper around each nullable column, which is a more elegant way than the mentioned CASE or IF solutions from below. – Tracheo

Note that the first argument in CONCAT_WS is a separator – Substantialism

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.

Scolopendrid

Answer 3

Try this:

SELECT id, concat(name , if(nickname is not null, concat(' (', nickname, ')'), '')) as name FROM user;

Guadiana

In my case i need to check for empty string, not null so my code is if(nickname != '') – Transmittance

Answer 4

You can use a CASE statement:

SELECT id, 
  case 
    when nickname is not null 
    then CONCAT(name,' (',nickname,')')
    else name end as name 
FROM user;

See SQL Fiddle with Demo

Or:

SELECT id, 
  concat(name, if(nickname is not null, concat(' (',nickname,')'), '')) as name
FROM user;

See SQL Fiddle with Demo

Both give the same result:

| ID |              NAME |
--------------------------
|  1 |  Adam (Alpha Dog) |
|  2 |       Bob (Bobby) |
|  3 | Charles (Charlie) |
|  4 |              Doug |

Lambeth