Forum Stats

  • 3,769,028 Users
  • 2,252,898 Discussions
  • 7,874,843 Comments

Discussions

Weird behaviour of LENGTH(NVL())

Hello

I was checking some not working DB code and found out there is a weird behaviour of checking NULL values:

SELECT LENGTH(NVL(NULL, '')) FROM DUAL;

-> Output: nothing (kinda null, expected: 0)

SELECT LENGTH(NVL(NULL, ' ')) FROM DUAL;

-> Output: 1 (what I expected)

Is there an explanation for this?

Experienced in Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,914 Red Diamond
    edited Mar 19, 2021 10:49AM Accepted Answer

    Oracle treats empty string as NULL so NVL(NULL, '') is meaningless. Yo could do:

    SQL> SELECT  NVL(LENGTH(NULL),0) L
      2    FROM  DUAL
      3  /
    
             L
    ----------
             0
    
    
    SQL>
    

    But is will not allow you to dostinguish between NULL and empty string.

    SY.

Answers