null vs empty string in Oracle

Question

Possible Duplicate    Why does Oracle 9i treat an empty string as NULL        I have a table in Oracle 10g named TEMP TABLE with only two columns - id and description just for the sake of demonstration   The column id is a sequence generated primary key of type NUMBER 35  0  not null and the column DESCRIPTION is a type of VARCHAR2 4000  not null   The basic table structure in this case would look something like the following    -------------- ----------- ---------------   Name            Null        Type             -------------- ----------- ---------------   ID              NOT NULL    NUMBER 35        DESCRIPTION     NOT NULL    VARCHAR2 4000    -------------- ----------- ---------------    After creating this table  I m trying to insert the following INSERT commands alternatively   INSERT INTO temp table  id  description  VALUES  1  null   - gt unsuccessful INSERT INTO temp table  id  description  VALUES  2         - gt unsuccessful   Both of them are unsuccessful as obvious because the not null constraint is enforced on the DESCRIPTION column   In both of the cases  Oracle complains  ORA-01400  cannot insert NULL into   WAGAFASHIONDB   TEMP TABLE   DESCRIPTION     An empty string is treated as a NULL value in Oracle     If I dropped the not null constraint on the DESCRIPTION column then the basic table structure would look like the following   -------------- ----------- ---------------   Name            Null        Type             -------------- ----------- ---------------   ID              NOT NULL    NUMBER 35        DESCRIPTION                 VARCHAR2 4000    -------------- ----------- ---------------    and both of the INSERT commands as specified would be successful  They would create two rows one with a null value and another with an empty string    in the DESCRIPTION column of the TEMP TABLE   Now  if I issue the following SELECT command   SELECT   FROM temp table WHERE description IS NULL    then it fetches both the rows in which one has a null value and the other has an empty string    in the DESCRIPTION column   The following SELECT statement however retrieves no rows from the TEMP TABLE  SELECT   FROM temp table WHERE description       It doesn t even retrieve the row which has an empty string in the DESCRIPTION column     Presumably  it appears that Oracle treats a null value and an empty string    differently here which however doesn t appear to be the case with the INSERT statement in which both a null value and an empty string    are prevented from being inserted into a column with a not null constraint  Why is it so

User · Answer

In oracle an empty varchar2 and null are treated the same, and your observations show that.

when you write:

select * from table where a = '';

its the same as writing

select * from table where a = null;

and not a is null

which will never equate to true, so never return a row. same on the insert, a NOT NULL means you cant insert a null or an empty string (which is treated as a null)

User · Answer

This is because Oracle internally changes empty string to NULL values  Oracle simply won t let insert an empty string    On the other hand  SQL Server would let you do what you are trying to achieve    There are 2 workarounds here    Use another column that states whether the  description  field is valid or not Use some dummy value for the  description  field where you want it to store empty string   i e  set the field to be  stackoverflowrocks  assuming your real data will never encounter such a description value    Both are  of course  stupid workarounds

[oracle] null vs empty string in Oracle

Examples related to oracle

Examples related to null

Examples related to oracle10g

Examples related to string