COALESCE() for blank (but not null) fields

mysql sql coalesce
Luke Shaheen picture Luke Shaheen · Nov 5, 2012 · Viewed 18.9k times · Source

I have two fields that I'm comparing with MySQL's function COALESCE(). For example, COALESCE(Field1, Field2). The problem is, Field1 is sometimes blank but not null; since it's not null COALESCE() selects Field1, even though its blank. In that case, I need it to select Field2.

I know I can write a if-then-else (CASE) statement in the query to check for this, but is there a nice simple function like COALESCE() for blank-but-not-null fields?

Answer

Andrea Ligios picture Andrea Ligios · Nov 5, 2012 
SELECT IFNULL(NULLIF(Field1,''),Field2)

NULLIF returns a NULL if Field1 is blank, while IFNULL returns Field1 if it's not blank or NULL and Field2 otherwise.

Related questions

Select a default value if returned result is NULL in MySQL

The following query runs fine, but I want to input the option that if the email returned is NULL, then I input "[email protected]" or something similar. I have read up on a few functions to do so, like …

Read More
MySQL, IFNULL(), COALESCE() on String not replacing

My statement should replace every empty title_column with 'no name', but it doesn't: SELECT COALESCE(main_table.title_column, 'no name') AS title FROM main_table; IFNULL() behaves the same way. What am I doing wrong ?

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More