5 Replies Latest reply: Dec 22, 2006 2:07 AM by Laurent Schneider RSS

    character value with a length of zero

    Laurent Schneider
      Hi,
      A tiny correction in

      Oracle® Database SQL Reference
      10g Release 2 (10.2)
      Part Number B14200-02
      Chapter 2
      Nulls
      sql_elements005.htm

      Oracle Database currently treats a character value with a length of zero as null

      It should be noted that this is not true for CLOB.
      SQL> create table t as select empty_clob() x from dual;
      
      Table created.
      
      SQL> select x from t where x is null;
      
      no rows selected
      
      SQL> select length(x) from t;
       LENGTH(X)
      ----------
               0
        • 1. Re: character value with a length of zero
          Nicolas.Gasparotto
          Hi Laurent,

          Interesting indeed.
          But what do you think about the following sentence in the same Note of doc :
          However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

          How do not treat empty string same as null ? What difference, currently, between null and empty string ?

          Nicolas.
          • 2. Re: character value with a length of zero
            Laurent Schneider
            it may change in a next release
            this was in Oracle 7 documentation and it is also in the Oracle 11 beta documentation
            the difference :
            SQL> select * from dual where ''='';
            
            no rows selected
            
            SQL> select * from dual where '' is null;
            D
            -
            X
            
            SQL> select length('') from dual;
            LENGTH('')
            ----------
            my point is about CLOB, see the difference
            SQL> create table t as select empty_clob() x from dual;
            
            Table created.
            
            SQL> select length(x) from t;
             LENGTH(X)
            ----------
                     0
            
            SQL> select count(*) from t where x is null;
              COUNT(*)
            ----------
                     0
            
            SQL> select count(*) from t where x is not null;
              COUNT(*)
            ----------
                     1
            • 3. Re: character value with a length of zero
              Laurent Schneider
              how to not treat empty string as null :
              well, difficult... maybe something like

              to check if x is empty:
              with t as (select '' x from dual)
              select count(*) from t where ('' is null and x is null) or (x='');
              I bet that Oracle is not going to change this before oracle 42 ;-)
              • 4. Re: character value with a length of zero
                275989
                Hi Laurent. A quick chat with the LOB gurus has convinced me that there's more to the story. Apparently an empty CLOB is SO empty that one cannot know what's in it -- not even null. Consider the following:

                SQL> create table b as select empty_clob() x from dual;
                Table created.

                SQL> select count(*) from b where x is null;

                COUNT(*)
                ----------
                0

                SQL> insert into b values ('');
                1 row created.

                SQL> select count(*) from b where x is null;

                COUNT(*)
                ----------
                1

                SQL> insert into b values (null);
                1 row created.

                SQL> select count(*) from b where x is null;

                COUNT(*)
                ----------
                2

                SQL> truncate table b;
                Table truncated.

                SQL> select count(*) from b where x is null;

                COUNT(*)
                ----------
                0

                In a perfect world, it would probably be easier to consider the content of an empty CLOB to be null. But alas, the world is not perfect. So I think I do need a tiny correction in the SQL Reference, but it is to clarify in the material on EMPTY_CLOB that this case is treated differently from a character value with a length of zero. Do you agree?

                Regards,
                Diana
                • 5. Re: character value with a length of zero
                  Laurent Schneider
                  Diana,
                  Thanks for your answer.
                  If length(empty_clob)=0, it does mean that the length of that string is zero, am I wrong?

                  Actually, it is not really related to empty clob, here is another example :
                  SCOTT@LSC01> create or replace function f return clob is begin return 'x'; end;
                    2  /
                  
                  Function created.
                  SCOTT@LSC01> select f,length(f) from dual;
                  F  LENGTH(F)
                  - ----------
                  x          1
                  
                  REPLACE(F,'X')  LENGTH(REPLACE(F,'X'))
                  --------------- ----------------------
                                                       0
                  
                  SCOTT@LSC01> select count(*) from dual where REPLACE(F,'X') is not null;
                    COUNT(*)
                  ----------
                           1
                  as you see, a not-null clob with a zero-length is not null

                  Regards
                  Laurent