I have a MySQL table which has about 30 columns. One column has empty values for the majority of the table. How can I use a MySQL command to filter out the items which do have values in the table?
Here is my attempt:
SELECT * FROM `table` WHERE column IS NOT NULL
This does not filter because I have empty cells rather that having NULL in the void cell.
Also look for the columns not equal to the empty string ''
SELECT * FROM `table` WHERE column IS NOT NULL AND column <> ''
If you have fields containing only whitespace which you consider empty, use TRIM() to eliminate the whitespace, and potentially leave the empty string ''
SELECT * FROM `table` WHERE column IS NOT NULL AND TRIM(column) <> ''
table WHERE column = "" worked for meA alternate approach that also handles blank spaces in a column as well as null:
SELECT * FROM `table` WHERE TRIM(column) > ''
Sometimes seems the value from a field is empty but the query filter ignores and still shows the result with empty fields. From my experience I recommend a trick:
SELECT * FROM `table` WHERE column IS NOT NULL AND CONCAT_WS(",",TRIM(column),TRIM(column)) <> ','
Very weird approach but works 100%!!
MySql says in documentation that
column IS NOT NULL AND TRIM(column) <> ''
will work but this does not happen always, trust me!!
