When doing an insert, an empty string is converted to null:
insert into test (f) values ('');
Now, there is a row with f containing a null.
But, when I query the table, I cannot use '':
select * from test where f='';
no rows selected
I can use null:
select * from test where f is null;
____F_
NULL
So… it appears that Oracle decided that empty strings cannot be used for insert, but they remain empty strings when doing queries. Where is the documentation on when an empty string becomes a null and when it remains an empty string?
Best Answer
This says it all:
SQL Fiddle
2 things:
1)
''
gets converted toNULL
on insert. That's an OracleVARCHAR2
thing.2)
select * from test where f='';
is trying to doselect * from test where f=NULL
, which isn't defined, and will return nothing becauseNULL
doesn't like the equality operator. You have to useIS NULL
orIS NOT NULL
.I'll add that the
CHAR
datatype behaves differently because it is padded.