Concatenating NULL and blank fields in MySQL

Posted 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       [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]

Comments

Nelson says:
May 20, 2013 at 01:42 am
Very nice solution!
Thanks a lot, solved my problem.
Flag as SPAM  |  Permalink
Alex says:
May 27, 2013 at 02:59 pm
Thanks Stan. This helped me with my problem.
Flag as SPAM  |  Permalink
Ankit says:
April 9, 2014 at 06:05 am
I am facing a problem in this solution, when middlename is null then there is 2 spaces between firstname and lastname
Flag as SPAM  |  Permalink

Leave your comment

Fields with * are required.

* When you submit a comment, you agree with Terms and Conditions of Use.