MySQL CONCAT returns NULL if any field contain NULL

Question

I have following data in my table "devices"

affiliate_name  affiliate_location  model     ip             os_type    os_version 

cs1             inter               Dell     10.125.103.25   Linux      Fedora  
cs2             inter               Dell     10.125.103.26   Linux      Fedora  
cs3             inter               Dell     10.125.103.27   NULL       NULL    
cs4             inter               Dell     10.125.103.28   NULL       NULL

I executed below query

SELECT CONCAT(`affiliate_name`,'-',`model`,'-',`ip`,'-',`os_type`,'-',`os_version`) AS device_name
FROM devices

It returns result given below

cs1-Dell-10.125.103.25-Linux-Fedora
cs2-Dell-10.125.103.26-Linux-Fedora
(NULL)
(NULL)

How to come out of this so that it should ignore NULL AND result should be

cs1-Dell-10.125.103.25-Linux-Fedora
cs2-Dell-10.125.103.26-Linux-Fedora
cs3-Dell-10.125.103.27-
cs4-Dell-10.125.103.28-

Just ran into this as well. I would consider near to insane that this is the default action. The exact opposite of programming with the least amount of surprises. — Joshua Pinter, Aug 24 '21 at 12:24

score 395 · Accepted Answer · answered Apr 01 '13 at 10:01

395

convert the NULL values with empty string by wrapping it in COALESCE

SELECT CONCAT(COALESCE(`affiliate_name`,''),'-',COALESCE(`model`,''),'-',COALESCE(`ip`,''),'-',COALESCE(`os_type`,''),'-',COALESCE(`os_version`,'')) AS device_name
FROM devices

answered Apr 01 '13 at 10:01

John Woo

258,903
69
498
492

1

You can use if select CONCAT(if(affiliate_name is null ,'',affiliate_name),'- ',if(model is null ,'',affiliate_name)) as model from devices – Dinesh Rabara Sep 24 '15 at 13:41
10

For those wonder, as I did, what the `COALESCE` function does: it returns the first non-`NULL` value parameter passed to it (or `NULL` if all parameters are `NULL`). By passing an empty string as the second parameter, you are guaranteeing it will not return `NULL`. – Jo. Mar 10 '17 at 17:03
7

mysql has IFNULL(arg, default) instead COALESCE with the same syntax – Vasilii Suricov Feb 19 '19 at 17:58
not work for me , still return null value when join column A = 'data person' with column B = null value . anyone know why ? – Yogi Arif Widodo Jan 20 '23 at 00:08
ah we cant use COALESCE(`column`, null) , i copied your code, and now its work . but for mysql 8 , we just use `COALESCE(column, '')` if we use `'`'` parenthesis , its return unknow column, and if we use null after define column, its return null if some value is null. – Yogi Arif Widodo Jan 20 '23 at 00:15
we cant added separator between `COALESCE` its still return null value – Yogi Arif Widodo Jan 20 '23 at 00:19
when use `COALESCE` every on `CONCAT` must be use `COALESCE` , if not do that still return on null value. – Yogi Arif Widodo Jan 20 '23 at 00:21

score 164 · Answer 2 · edited Oct 30 '15 at 17:13

164

Use CONCAT_WS instead:

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

SELECT CONCAT_WS('-',`affiliate_name`,`model`,`ip`,`os_type`,`os_version`) AS device_name FROM devices

edited Oct 30 '15 at 17:13

mwfearnley

3,303
2
34
35

answered Apr 01 '13 at 10:03

Gurmeet

3,094
4
19
43

Sorry neeraj i missed the '_' between Concat and WS Please try again with CONCAT_WS().I have updated the answer please check, – Gurmeet Apr 01 '13 at 10:14
12

Note that this solution hides a complete "column" (including the separator) if one of the middle fields is NULL. So this answer is only correct assuming that only the last field(s) can be NULL. Depending on your need, the COALEASE() answer below might be better. – Jannes Nov 11 '14 at 14:54
1

This only works if you want every member separated by the same separator. CONCAT doesn't have this limitation. I posted the solution as an answer here – patrick Jan 05 '15 at 13:05
Dangerous: `SELECT CONCAT_WS(';',1,NULL)` is identical with `SELECT CONCAT_WS(';',NULL,1)`. Both gives `1`. This is a problem if you want `1;` respectively `;1`. – giordano Oct 21 '22 at 17:17

score 19 · Answer 3 · edited Apr 19 '19 at 15:30

19

CONCAT_WS still produces null for me if the first field is Null. I solved this by adding a zero length string at the beginning as in

CONCAT_WS("",`affiliate_name`,'-',`model`,'-',`ip`,'-',`os_type`,'-',`os_version`)

however

CONCAT("",`affiliate_name`,'-',`model`,'-',`ip`,'-',`os_type`,'-',`os_version`)

produces Null when the first field is Null.

edited Apr 19 '19 at 15:30

Michael

8,362
6
61
88

answered Nov 27 '14 at 00:04

Ken4Edge

207
2
2

obviously, because the first field is the string that it will concatenate with (WS = with string) – Bouke Versteegh Feb 07 '15 at 01:17
4

CONCAT_WS is short for Concatenate With Separator. The first parameter is the separator and cannot be null. This is probably what you want instead: `CONCAT_WS("-", affiliate_name, model, ip, os_type, os_version)` – encrest Dec 03 '15 at 02:10

score 14 · Answer 4 · answered Jan 05 '15 at 13:04

14

To have the same flexibility in CONCAT_WS as in CONCAT (if you don't want the same separator between every member for instance) use the following:

SELECT CONCAT_WS("",affiliate_name,':',model,'-',ip,... etc)

answered Jan 05 '15 at 13:04

patrick

11,519
8
71
80

Shamsul Arefin · Answer 5 · 2021-04-13T21:33:02.883

Reason:

MySQL :: Reference Manual :: 12.8 String Functions and Operators says:

CONCAT() returns NULL if any argument is NULL.

Solution:

MySQL :: Reference Manual :: 12.5 Flow Control Functions says:
IFNULL(expr1,expr2) 
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

SELECT
    CONCAT(
        IFNULL(`affiliate_name`, ''),
        '-',
        IFNULL(`model`, ''),
        '-',
        IFNULL(`ip`, ''),
        '-',
        IFNULL(`os_type`, ''),
        '-',
        IFNULL(`os_version`, '')
    ) AS device_name
FROM
    devices

score 11 · Answer 6 · answered Apr 01 '13 at 10:15

11

SELECT CONCAT(isnull(`affiliate_name`,''),'-',isnull(`model`,''),'-',isnull(`ip`,''),'-',isnull(`os_type`,''),'-',isnull(`os_version`,'')) AS device_name
FROM devices

answered Apr 01 '13 at 10:15

Harshil

403
2
7

20

in mysql IFNULL() instead of ISNULL() – Jeffrey Nicholson Carré Jan 16 '15 at 17:33

score 3 · Answer 7 · answered Sep 24 '15 at 13:42

3

you can use if statement like below

select CONCAT(if(affiliate_name is null ,'',affiliate_name),'- ',if(model is null ,'',affiliate_name)) as model from devices

answered Sep 24 '15 at 13:42

Dinesh Rabara

1,119
12
13

MySQL CONCAT returns NULL if any field contain NULL

7 Answers7

Linked

Related