Concatenating NULL and blank fields in MySQL

If you ever need to concatenate fields that are empty or equal null, you may run into troubles because MySQL doesn't concatenate NULL-fields as you might expect - if there is one of the concatenating fields equals NULL, the whole concatenating value will be NULL.

See the following dummy table:

firstname | middlename | lastname | email
John        J.           Smith      [email protected]
Amanda      NULL         Smith      [email protected]

As you can see Amanda Smith doesn't have middle name. So, if you now run a query that will try to concatenate first name, middle name and last name, you'll get NULL value instead of expected concatenated value.

SELECT CONCAT(firstname,' ',middlename,' ',lastname) AS user, email FROM users

name            |   email
John J. Smith       [email protected]
NULL                [email protected]

To solve this issue you can use a MySQL function IFNULL which return a custom value if given value is NULL.

SELECT CONCAT(IFNULL(firstname,''),' ',IFNULL(middlename,''),' ',IFNULL(lastname,'')) AS user, email FROM users

Now we get the expected result:

name            |   email
John J. Smith       [email protected]
Amanda Smith        [email protected]