Problem
How can I determine if a column in a table’s rows is empty or null?
(e.g. null or '' or ' ' or ' ' and ...)
Asked by priya
Solution #1
All rows where some col is NULL or ” will be selected (empty string)
SELECT * FROM table WHERE some_col IS NULL OR some_col = '';
Answered by maček
Solution #2
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
As a result, regardless of how many spaces make up the some col value, this should work:
SELECT *
FROM T
WHERE some_col IS NULL
OR some_col = ' ';
or more succinctly:
SELECT *
FROM T
WHERE NULLIF(some_col, ' ') IS NULL;
Answered by onedaywhen
Solution #3
A shorter version of the condition is:
WHERE some_col > ''
Since null > ” produces unknown, this has the effect of filtering out both null and empty strings.
Answered by Andomar
Solution #4
You can use WHERE col IS NULL or WHERE col IS NOT NULL to see if a column is null or not, for example.
SELECT myCol
FROM MyTable
WHERE MyCol IS NULL
You have a variety of white space possibilities in your example. You can use TRIM to remove white space and COALESCE to default a NULL value (COALESCE returns the first non-null value from the values you supply).
e.g.
SELECT myCol
FROM MyTable
WHERE TRIM(COALESCE(MyCol, '')) = ''
This last query will return rows with MyCol equal to null or any length of whitespace.
If at all possible, avoid using a function on a column in the WHERE clause because it makes using an index more difficult. If you just want to see if a column is null or empty, this would be a better option:
SELECT myCol
FROM MyTable
WHERE MyCol IS NULL OR MyCol = ''
For further information, see TRIM COALESCE and IS NULL.
Working with null values is also covered in the MySQL documentation.
Answered by Code Magician
Solution #5
Try this strategy if you don’t want to use WHERE.
Empty and NULL values will be selected.
SELECT ISNULL(NULLIF(fieldname,'')) FROM tablename
Answered by PodTech.io
Post is based on https://stackoverflow.com/questions/8470813/how-do-i-check-if-a-column-is-empty-or-null-in-mysql