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;

Something to point out that threw me off at first. When nickname is null then CONCAT('(',nickname,')') will also return null. — Timo Huovinen, Feb 07 '13 at 11:47

fthiella · Accepted Answer · 2012-12-13T19:25:30.693

21

You could use CONCAT_WS which skips null strings:

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

edited Dec 13 '12 at 19:25

answered Dec 13 '12 at 19:17

fthiella

48,073
15
90
106

if nickname is NULL you will get NULL as result – Sir Rufo Dec 13 '12 at 19:18
2

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. – t.h3ads Mar 25 '21 at 13:33

spencer7593 · Answer 2 · 2012-12-13T19:26:19.910

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.

Saharsh Shah · Answer 3 · 2012-12-14T07:18:48.953

2

Try this:

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

edited Dec 14 '12 at 07:18

answered Dec 13 '12 at 19:17

Saharsh Shah

28,687
8
48
83

In my case i need to check for empty string, not null so my code is ```if(nickname != '')``` – Ronald May 15 '21 at 17:55

Taryn · Answer 4 · 2012-12-13T19:23:46.963

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 |

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

4 Answers4