Home > Blogroll, sql > length(”)=null?

length(”)=null?

December 20th, 2006
Goto comments Leave a comment

What is the length of an empty string?

According to Oracle documentation,

Oracle Database currently treats a character value with a length of zero as null. 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

And therefore the length of null is null, not 0 (there are no character value with a length of zero :oops:).

However, this is not true for clobs :twisted: 


SQL> create table t(x clob);
Table created.
SQL> insert into t values (empty_clob());
1 row created.
SQL> select x,length(x) from t where x is not null;
X  LENGTH(X)
- ----------
           0

Bookmark and Share

  1. Mihajlo Tekic
    December 20th, 2006 at 17:48 | #1
    Reply | Quote

    Laurent,

    There is a difference between NULL value for a CLOB and EMPTY CLOB.
    Therefore, I think the difference in lengths makes sense.

    
SQL> create table t
  2  ( x clob,
  3  y clob);

    
Table created.

    
SQL> insert into t values (null, empty_clob());

    
1 row created.

    
SQL> select length(x),length(y) from t;

    
 LENGTH(X)   LENGTH(Y)
---------- ----------
                    0

  2. laurentschneider
    December 20th, 2006 at 18:19 | #2
    Reply | Quote

    Hi,
    I formatted your post. Somehow wordpress sucks, to get a right formatting, I have to put </code><code> on each blank line of code. Do not ask why.

    I have the same opinion, it makes sense to have empty_clob is not null, and it makes no sense to have ” is null, I just wanted to point out the difference, because I saw length(f)=0 for the first time this morning :cool:

  3. Steven Feng
    December 20th, 2006 at 19:50 | #3
    Reply | Quote

    I believe Empty_clob() initialized the clob object to have the pointer. That is why it is not null anymore.

    thanks.

  4. Rob V
    January 4th, 2007 at 23:57 | #4
    Reply | Quote

    Yes, the length of NULL is NULL. How can you know the length of something you don’t know? Do you know the length of my middle name? Of course not. But it’s not 0, I think you know that much!

  5. laurentschneider
    January 5th, 2007 at 09:54 | #5
    Reply | Quote

    Steven: it is not due to empty clob. I have posted an example on the documentation feedback forum :

    
SQL> create or replace function f return clob is begin return 'x'; end;
2  /
Function created.
SQL> select f,length(f) from dual;

F  LENGTH(F)
- ----------
x          1

REPLACE(F,'X')  LENGTH(REPLACE(F,'X'))
--------------- ----------------------
                                     0

SQL> select count(*) from dual where REPLACE(F,'X') is not null;
  COUNT(*)
----------
         1

  6. laurentschneider
    January 5th, 2007 at 10:01 | #6
    Reply | Quote

    Rob: it is clear that NULL IS NULL, but as 0 IS NOT NULL, ” should not be NULL

    The fact that a 0 length string is null is not correct and it may change in a next release.

  7. laurentschneider
    January 5th, 2007 at 10:24 | #7
    Reply | Quote

    note 0 is not null is quite tricky when you speak german, because 0 is spoken NULL in german ;)
    dict.leo.org

  8. Rob V
    January 5th, 2007 at 21:04 | #8
    Reply | Quote

    “The fact that a 0 length string is null is not correct ”

    Bingo. The root of all confusion here.

    “note 0 is not null is quite tricky when you speak german, because 0 is spoken NULL in german”

    Then stop speaking German. You’ve seen Star Trek, we all settle on English eventually.

  9. laurentschneider
    January 6th, 2007 at 09:36 | #9
    Reply | Quote

    Well, the trick is pronounce 0 in German and NULL in english, kind of
    is not

    or start learning klingonish ;-)

  1. No trackbacks yet.