Is it possible to store '' (empty string) as a non NULL value in the database?

Question

I am using Oracle DB. At the database level, when you set a column value to either NULL or '' (empty string), the fetched value is NULL in both cases. Is it possible to store '' (empty string) as a non NULL value in the database?

I execute this

UPDATE contacts SET last_name = '' WHERE id = '1001';

commit;

SELECT last_name, ID FROM contacts WHERE id ='1001';

LAST_NAME                  ID
------------               ------
null                       1001

Is it possible to store the last_name as a non-NULL empty string ('')?

Answer 1

The only way to do this in oracle is with some kind of auxiliary flag field, that when set is supposed to represent the fact that the value should be an empty string.

Answer 2

A long time since I used Oracle, but I believe we used to use a single space ' ' to represent an empty string, then trim it after reading.

Answer 3

Oracle has a well know behavior that it silently converts "" to NULL on INSERT and UPDATE statements.

Answer 4

As far as i know Oracle does not distinguish between '' and NULL, see here.

Answer 5

Building on recursive's response:

You can set up a trigger to make life easier when you're performing inserts or updates (so that you don't have to set a flag manually).

I use a date here, but any flag would do.

  CREATE TABLE x  (mycolumn     VARCHAR2(10) NULL,
                   last_updated DATE         NULL);

  CREATE TRIGGER my_trigger
  BEFORE UPDATE OR INSERT ON x
  FOR EACH ROW
  DECLARE
  BEGIN
      IF (:NEW.mycolumn IS NOT NULL) THEN
        :NEW.last_modified := SYSDATE;
      ELSE
        :NEW.last_modified := NULL; -- Leave this out if the field cannot become unknown after being known.
      END IF;
   END;

  INSERT INTO x VALUES ('', NULL);
  INSERT INTO x VALUES ('TEST', NULL);
  INSERT INTO x VALUES ('', NULL);
  INSERT INTO x VALUES ('TEST', NULL);

  SELECT * FROM X;