sql - MySQL CONCAT returns NULL if any field contain NULL -
i have following data in 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`) device_name 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 come out of should ignore null , 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-
convert null
values empty string wrapping in coalesce
select concat(coalesce(`affiliate_name`,''),'-',coalesce(`model`,''),'-',coalesce(`ip`,''),'-',coalesce(`os_type`,''),'-',coalesce(`os_version`,'')) device_name devices
Comments
Post a Comment