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;

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?


4 Answers 4


This says it all:

select NVL('','it is null') as value
from dual;

SQL Fiddle

2 things:

1) '' gets converted to NULL on insert. That's an Oracle VARCHAR2 thing.

2) select * from test where f=''; is trying to do select * from test where f=NULL, which isn't defined, and will return nothing because NULL doesn't like the equality operator. You have to use IS NULL or IS NOT NULL.

I'll add that the CHAR datatype behaves differently because it is padded.

  • Oracle treats empty string '' as NULL. Commented Feb 22, 2019 at 7:33

Oracle treats '' and NULL the same. When inserting '', there is no conversion of '' to NULL, merely an interpretation of '' as NULL in the same way that the word NULL is interpreted as NULL or rtrim('a','a') is interpreted as NULL.

Here is a demonstration using the following table and insert:

drop table t1;
create table t1 (c1 varchar2(10));
insert into t1 (c1) values ('');

The insert above inserted a NULL value for c1. You can select that row as follows:


When you add a WHERE clause to compare equality and one of the values being compared is NULL, the result will always be unknown. Unknown will evaluate to false except that further operations on an unknown value produce unknown values. All of the following return no rows because the WHERE clauses contain conditions that will never be true regardless of the data.

SELECT c1 FROM t1 WHERE c1 = '';
SELECT c1 FROM t1 WHERE '' = '';

Oracle provides a special syntax to retrieve rows with a particular column having null values -- IS NULL.


There are a few conditions in which oracle compares NULLS treating them as equal to other NULL values such as in DECODE statements and in compound keys.

More information can be found in the SQL Language Reference.

  • Sorry Leigh, wrote answers at the same time :)
    – Philᵀᴹ
    Commented Sep 12, 2013 at 19:52
  • @Phil I liked your answer. It was direct and clear. What I'm seeing is not an issue about '' and NULL. It is an issue about =NULL and IS NULL.
    – kainaw
    Commented Sep 12, 2013 at 20:28
  • @Phil Not a problem. I think you addressed the real core of the confusion. Commented Sep 12, 2013 at 20:57

Oracle RDBMS is not making any differences between null and empty strings; meaning that there is no transformation to convert an empty string into a null value.

I can be understood that none of the 2 is containing information.

  • That cannot be true. It must be doing a conversion. If I insert a '', and there is no conversion, I will be able to select a ''. But, I cannot. I must select a NULL to get the '' I inserted.
    – kainaw
    Commented Sep 12, 2013 at 16:15
  • 2
    You get no results using WHERE f= '' for the same reason you get no results using WHERE f=NULL. In Oracle '' and NULL are the same so they both evaluate to false. Commented Sep 12, 2013 at 17:51
  • Oracle converts zero length varchars to NULLs. If you will create your table with f not null constraint your insert will fail with "cannot insert NULL" error. Commented Sep 12, 2013 at 18:30
  • @LeighRiffel Your answer is based on an incorrect reading of the results. When using '', you get no results. When using NULL, you get a result with NULL in it. There is a huge difference between getting no result and getting a record with a NULL in it. Imagine if there were two columns. Using '', you get no records. Using NULL, you get the value from the first column and a NULL value for the second - which is not "no record".
    – kainaw
    Commented Sep 12, 2013 at 18:38
  • 1
    @kainaw Sorry, you don't understand!
    – Philᵀᴹ
    Commented Sep 12, 2013 at 19:54

This explains it a little:

create table t1 (c1 char(1),
                 c2 char(3),
                 c3 varchar2(10));

insert into t1 values ('','','');
insert into t1 values ('a','a','a');

v1 char(1);
v1 := '';
insert into t1 values (v1,v1,v1);

select * from t1;

select count(c1) from t1;
select count(c2) from t1;
select count(c3) from t1;

select length(c1), length(c2), length(c3) from t1;
  • 1
    Hi there, welcome to the site. Could you explain how your code works and how it explains the OP's question?
    – Tom V
    Commented Oct 6, 2016 at 7:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.