length(”)=null?

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 😈


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

9 thoughts on “length(”)=null?”

  1. Mihajlo Tekic

    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 Post author

    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 😎

  3. Steven Feng

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

    thanks.

  4. Rob V

    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 Post author

    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 Post author

    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.

  8. Rob V

    “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.

Comments are closed.