Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as extoperSQL> set serveroutput on SQL> SQL> DECLARE 2 test VARCHAR2(20) := ''; 3 BEGIN 4 IF test IS NULL 5 THEN 6 dbms_output.put_line(''''' is equal to NULL'); 7 END IF; 8 9 IF test = '' 10 THEN 11 dbms_output.put_line(''''' is equal to '''''); 12 END IF; 13 END; 14 / '' is equal to NULL PL/SQL procedure successfully completed SQL>
This is a very old fact about Oracle. It seems to annoy people who are new to the product. Old lags such as myself take a pragmatic view: what actually is the difference between a NULL and an empty string? People who claim there is a difference really don’t understand the concept of NULL. It is the absence of meaning.
Anyway, your message is wrong. Nothing is equal to NULL 😉
“what actually is the difference between a NULL and an empty string?”
If you consider a string as a sequence or array of characters, then an empty string can validly be a string that is known to consist of zero characters.
This would be useful, for example, when you are using the TRANSLATE function to remove specific characters. Rather than specifying something like TRANSLATE(col,”,”), I have to supply a non-empty string TRANSLATE(col,’a’,’a’).
Personally, I prefer that other databases are quite happy to differentiate between an empty string and a null/unknown string.