MySQL CONCAT returns NULL if any field contain NULL

Question

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-

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT CONCAT isnull  affiliate name       -  isnull  model       -  isnull  ip       -  isnull  os type       -  isnull  os version       AS device name FROM devices

User · Answer

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

[mysql] MySQL CONCAT returns NULL if any field contain NULL

Examples related to mysql

Examples related to sql

Examples related to null

Examples related to concat