mySQL return not empty field

Question

I have two fields, but I want to return only the non-empty one.

select firstname, lastname, (if firstname != empty, then firstname else lastname) as name from users;

How do I go about doing something like above. Am not sure what should be inside the round brackets. I don't want to use PHP post-processing.

Answer 1

There are several ways you can do this in MySQL.

You can use the coalesce() function which returns the first non-null value among its arguments.

select coalesce(firstname, lastname) as name from users;

Alternatively, you could use a case statement:

select case when firstname is not null then firstname else lastname end as name from users;

Or you could use the if() function:

select if(firstname is not null, firstname, lastname) as name from users;

Or you could use the ifnull() function:

select ifnull(firstname, lastname) as name from users;

Answer 2

This works for both null values as well as empty strings ('')

SELECT IF(LENGTH(firstname), firstname, lastname) as name from users;