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;

SELECT 
    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

Reference:

http://stackoverflow.com/questions/15980995/in-mysql-can-you-check-if-a-field-is-null-or-empty-using-only-one-comparison

No comments: