Concatenating NULL and blank fields in MySQLPosted by Stanislav Furman on May 17, 2013
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 johnsmit[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 johnsmit[email protected] Amanda Smith [email protected]
Thanks a lot, solved my problem.
Leave your comment