Menu
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?
893
The Coalesce function evaluates its arguments in order and returns the first value that isn't blank or an empty string. Use this function to replace a blank value or empty string with a different value but leave non-blank and non-empty string values unchanged.
The COALESCE function evaluates its arguments from left to right. It stops evaluating until it finds the first non-NULL argument. It means that all the remaining arguments are not evaluated at all. The COALESCE function returns NULL if all arguments are NULL .
If all the values in MySQL COALESCE() function are NULL then it returns NULL as the output. It means that this function does not find any non-NULL value in the list.
Comparing COALESCE and ISNULL Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.
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.
77
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
