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, Data Engineer specialising in PHP, ReactJS, Tableau, AWS RedShift and Matillion ETL

Based in Melbourne, Australia

Feel free to contact me justin@kelly.org.au or _justin_kelly

Leave a comment

Name Notify me of replies by email.