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

Popular posts from this blog

Delphi XE2 Indy10 udp client-server interchange using SendBuffer-ReceiveBuffer -

Qt ActiveX WMI QAxBase::dynamicCallHelper: ItemIndex(int): No such property in -

Enable autocomplete or intellisense in Atom editor for PHP -