Forum Stats

  • 3,728,545 Users
  • 2,245,651 Discussions
  • 7,853,592 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,286 Black Diamond
    edited March 19 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

Sign In or Register to comment.