empty string in oracle

Question

When I insert an empty String in a database table column, it looks like it is internally stored as NULL. So why can I not select the respective row by looking for exactly that empty string?

Example:

insert into mytable (mycolumn,col2,col3) values ('','xyz','abc');

// now there is a row having mycolumn == NULL

select * from mytable where mycolumn='';

// empty :(

What can I do about that?

Answer 1

This is a weird anachronism in Oracle (using default settings). Oracle does, indeed, treat an empty string as NULL. This includes in comparisons, so:

where mycolumn = ''

is the same as:

where mycolumn = NULL

And this never returns true (NULL <> NULL).

My advice? Get used to using NULL explicitly and writing:

where mycolumn is null