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-
Solution 1:
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
Solution 2:
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
Solution 3:
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.