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
– Joshua Pinter, Commented Aug 24, 2021 at 12:24

John Woo · Accepted Answer · 2013-04-01 10:01:32Z

419

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 1, 2013 at 10:01

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Dinesh Rabara Over a year ago

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

Jo. Over a year ago

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.

Vasilii Suricov Over a year ago

mysql has IFNULL(arg, default) instead COALESCE with the same syntax

yogithesymbian Over a year ago

not work for me , still return null value when join column A = 'data person' with column B = null value . anyone know why ?

yogithesymbian Over a year ago

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.

|

mwfearnley · Accepted Answer · 2015-10-30 17:13:01Z

176

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, 2015 at 17:13

mwfearnley

3,8262 gold badges39 silver badges39 bronze badges

answered Apr 1, 2013 at 10:03

Gurmeet

3,3245 gold badges23 silver badges45 bronze badges

4 Comments

Gurmeet Over a year ago

Sorry neeraj i missed the '_' between Concat and WS Please try again with CONCAT_WS().I have updated the answer please check,

Jannes Over a year ago

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.

patrick Over a year ago

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

giordano Over a year ago

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.

Michael come lately · Accepted Answer · 2019-04-19 15:30:40Z

21

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, 2019 at 15:30

Michael come lately

9,6418 gold badges70 silver badges97 bronze badges

answered Nov 27, 2014 at 0:04

Ken4Edge

2352 silver badges2 bronze badges

2 Comments

Bouke Versteegh Over a year ago

obviously, because the first field is the string that it will concatenate with (WS = with string)

encrest Over a year ago

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)

patrick · Accepted Answer · 2015-01-05 13:04:19Z

16

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 5, 2015 at 13:04

patrick

11.8k8 gold badges75 silver badges81 bronze badges

Comments

Shamsul Arefin · Accepted Answer · 2021-04-13 21:33:02Z

13

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

edited Apr 13, 2021 at 21:33

answered Apr 13, 2021 at 21:13

Shamsul Arefin

7017 silver badges15 bronze badges

Comments

Harshil · Accepted Answer · 2013-04-01 10:15:32Z

12

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

answered Apr 1, 2013 at 10:15

Harshil

4112 silver badges7 bronze badges

1 Comment

Jeffrey Nicholson Carré Over a year ago

in mysql IFNULL() instead of ISNULL()

Dinesh Rabara · Accepted Answer · 2015-09-24 13:42:26Z

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, 2015 at 13:42

Dinesh Rabara

1,12712 silver badges13 bronze badges

Comments

Sergio Abreu · Accepted Answer · 2024-11-09 17:41:52Z

0

An important TIP: Variables should have DIFFERENT NAMES from columns or it can cause failure.

DECLARE CAMPO    ...... SELECT CAMPO.... FETCH INTO CAMPO = FAILURE
DECLARE mycampo  .......SELECT CAMPO...  FETCH INTO mycampo = SUCCESS

Info: https://bugs.mysql.com/bug.php?id=20834

answered Nov 9, 2024 at 17:41

Sergio Abreu

2,98927 silver badges22 bronze badges

Collectives™ on Stack Overflow

MySQL CONCAT returns NULL if any field contain NULL

8 Answers 8

7 Comments

4 Comments

2 Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

7 Comments

4 Comments

2 Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related