This is a strange one, and I don't have anything to back it up, just one occurrence.

I attempted a CONCAT of a few fields, some of them being NULL values. If a row contained a field with a NULL value, the resulting concatenated string was also null.

This leads me to conclude that in the language of MySQL,

NULL + string = NULL

So, if you plan on using CONCAT on a particular set of fields, perhaps make them contain empty string values, as opposed to NULL.

Tags:

2 Comments

  1. Note: The same applies to GROUP_CONCAT. If you are concatenating fields which might contain value of NULL, expect the unexpected. Using the COALESCE function will help, i.e.

    SELECT COALESCE(fieldname,'') + string

  2. I've discovered that the NULL issue also applies to arithmetic functions.  If you try to add a number to NULL, the result is NULL. So, the COALESCE function will come in handy there as well.

Leave a Reply

Your email address will not be published. Required fields are marked *