COALESCE() for blank (but not null) fields
20,912
Solution 1
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.
Solution 2
I know I'm late to the party here, but there is a way to do this while still using COALESCE(). This would then work if your value was NULL or ''.
Select COALESCE(NULLIF(Field1,''), Field2)
Solution 3
You can use a CASE
expression:
CASE WHEN Field1 <> '' THEN Field1 ELSE Field2 END
Related videos on Youtube
Author by
Luke Shaheen
Updated on July 09, 2022Comments
-
Luke Shaheen almost 2 years
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 nullCOALESCE()
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? -
Luke Shaheen over 11 yearsThat's what I meant by if-then-else; I was looking for a cleaner, one-line solution
-
Luke Shaheen over 11 yearsI could do the same, but with COALESCE correct?
COALESCE(NULLIF(Field1,''),Field2)
- is there a speed/processing load difference? -
Luke Shaheen over 11 years
-
Luke Shaheen over 10 yearsSee the comment on the selected answer :)
-
Beachhouse over 10 yearsWell there you go! Didn't see that.