Question

MySQL CONCAT returns NULL if any field contain NULL

Ask Question

I have the 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-

Answer 1

Try to convert the NULL values with empty string by wrapping it in COALESCE like this:

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

FROM devices