Tuesday, August 25, 2015

In MySQL, can you check if a field is NULL or empty using only one comparison?

Use COALESCE() to 'normalize' the value (convert NULL values to an empty string);

WHERE COALESCE(mycolumn, '') = ''

Read the documentation: COALESCE()

Or the other way around; convert empty strings to NULL;

WHERE NULLIF(mycolumn, '') IS NULL

Documentation: NULLIF()

Of those two, I would prefer COALESCE() as it is part of the ANSI SQL standard

You can experiment with it yourself, just do this;

    mycolumn                      AS orig_value,
    COALESCE(mycolumn, '')        AS coalesce_value,
    (COALESCE(mycolumn, '') = '') AS compare_result
FROM mytable;

This will show the original value, the 'coalesce' value and the result of the comparison side by side for every row in the table



No comments: