Select fields not equal to the empty string in oracle

Question

I'm trying to write a query that simply selects all non-empty names. Both the following queries return no results:

 SELECT name FROM MyTable WHERE name != '';

 SELECT name FROM MyTable WHERE name = '';

For context, both of these queries do return results:

 SELECT name FROM MyTable WHERE name != 'a';

 SELECT name FROM MyTable WHERE name IS NOT NULL;

I read somewhere that the empty string is equivalent to NULL in oracle, but I still don't see why that explains this behaviour. I need to support both SQL Server and Oracle which is why I can't just rely on WHERE name IS NOT NULL

Can anyone explain what's happening here? Thanks!

Answer 1

Any comparison that involves a NULL value will always return FALSE.

Answer 2

Can anyone explain what's happening here? Thanks!

From the Ask Tom archive.

A ZERO length varchar is treated as NULL.

'' is not treated as NULL.

'' when assigned to a char(1) becomes ' ' (char types are blank padded strings).

'' when assigned to a varchar2(1) becomes '' which is a zero length string and a zero length string is NULL in Oracle (it is no longer '')