How do I check if a column is empty or null in MySQL?

I have a column in a table which might contain null or empty values. How do I check if a column is empty or null in the rows present in a table?

(e.g. null or '' or '  ' or '      ' and ...)

This will select all rows where some_col is NULL or '' (empty string)

SELECT * FROM table WHERE some_col IS NULL OR some_col = '';

As defined by the SQL-92 Standard, when comparing two strings of differing widths, the narrower value is right-padded with spaces to make it is same width as the wider value. Therefore, all string values that consist entirely of spaces (including zero spaces) will be deemed to be equal e.g.

'' = ' ' IS TRUE
'' = '  ' IS TRUE
' ' = '  ' IS TRUE
'  ' = '      ' IS TRUE
etc

Therefore, this should work regardless of how many spaces make up the some_col value:

SELECT * 
  FROM T
 WHERE some_col IS NULL 
       OR some_col = ' ';

or more succinctly:

SELECT * 
  FROM T
 WHERE NULLIF(some_col, ' ') IS NULL;

A shorter way to write the condition:

WHERE some_col > ''

Since null > '' produces unknown, this has the effect of filtering out both null and empty strings.

You can test whether a column is null or is not null using WHERE col IS NULL or WHERE col IS NOT NULL e.g.

SELECT myCol 
FROM MyTable 
WHERE MyCol IS NULL

In your example you have various permutations of white space. You can strip white space using TRIM and you can use COALESCE to default a NULL value (COALESCE will return the first non-null value from the values you suppy.

e.g.

SELECT myCol
FROM MyTable
WHERE TRIM(COALESCE(MyCol, '')) = ''

This final query will return rows where MyCol is null or is any length of whitespace.

If you can avoid it, it's better not to have a function on a column in the WHERE clause as it makes it difficult to use an index. If you simply want to check if a column is null or empty, you may be better off doing this:

SELECT myCol
FROM MyTable
WHERE MyCol IS NULL OR MyCol =  ''

See TRIM COALESCE and IS NULL for more info.

Also Working with null values from the MySQL docs

How do I check if a column is empty or null in MySQL?

Related

Recent Posts