Google Sheet Query: Why "!= ' '" doesn't work?

Question

To select cells that are not empty, we have to use something like

SELECT * WHERE A IS NOT NULL

If I replace it with

   SELECT * WHERE A != ''

or

   SELECT * WHERE A != ' '

There will be no error but nothing is returned.

Why is this?

Answer 1

when referring to a null cell try:

"where A <> ''"

or:

"where not A = ''"

or:

"where not A matches ''"

or:

"where not A matches '^$'"

or:

"where not A like ''"

but even yours:

"where A != ''"

and:

"where A is not null"

will work...

the real issue is the shabby query behavior when you got a mixed dataset (numeric + plain text) and query makes the wrong assumption that you don't need the minority of the set:

where in such case you will need to force the formatting like:

=ARRAYFORMULA(QUERY(A:A&"", "where Col1 is not null", ))

notice the Col1 syntax

but there are cases when you need not to convert numeric values into plain text values so in such case it is better to use:

=FILTER(A:A, A:A<>"")