Justin Kelly various ramblings

Workaround for the MySQL concat() returns NULL if any fields are NULL problem

In MySQL the CONCAT funcation returns NULL if any of the concated fields are NULL, who knew? - so even if all the fields except 1 have valid data it will return NULL

if your stuck on this the workaournd is to put an IFNULL in the field, refer example below:

SELECT CONCAT(IFNULL(fName1,''),' ',IFNULL(mName2,''),' ',IFNULL(lName3,'')) AS userName

or use CONCAT_WS - which allows for null - but requires a seperator to be defined

Justin Kelly

Justin Kelly

Web Developer | Business Analytics | Library Technology based in Melbourne, Australia

Contact me Follow on Twitter