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.
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;
This works for both null values as well as empty strings ('')
SELECT IF(LENGTH(firstname), firstname, lastname) as name from users;
