CODEWITHSUNDEEP
mysqlsqlnullconcatenation
Neeraj
Neeraj

Reputation: 9155

MySQL CONCAT returns NULL if any field contain NULL

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-

Upvotes: 263

Views: 201661

Answers (8)

Sergio Abreu
Sergio Abreu

Reputation: 2899

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

Upvotes: 0

Shamsul Arefin
Shamsul Arefin

Reputation: 701

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

Upvotes: 13

Ken4Edge
Ken4Edge

Reputation: 235

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.

Upvotes: 21

Gurmeet
Gurmeet

Reputation: 3314

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

Upvotes: 175

Dinesh Rabara
Dinesh Rabara

Reputation: 1127

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

Upvotes: 3

patrick
patrick

Reputation: 11721

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)

Upvotes: 16

Harshil
Harshil

Reputation: 411

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

Upvotes: 12

John Woo
John Woo

Reputation: 263713

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

Upvotes: 417

Related Questions