Oracle PL/SQL string compare issue

Question

I have the following Oracle PL/SQL codes that may be rusty from you guys perspective:

 DECLARE  str1  varchar2(4000);  str2  varchar2(4000);  BEGIN    str1:='';    str2:='sdd';    IF(str1<>str2) THEN     dbms_output.put_line('The two strings is not equal');    END IF;  END;  / 

This is very obvious that two strings str1 and str2 are not equal, but why 'The two strings are not equal' was not printed out? Do Oracle have another common method to compare two string?

Answer 1

As Phil noted, the empty string is treated as a NULL, and NULL is not equal or unequal to anything. If you expect empty strings or NULLs, you'll need to handle those with NVL():

 DECLARE  str1  varchar2(4000);  str2  varchar2(4000);  BEGIN    str1:='';    str2:='sdd'; -- Provide an alternate null value that does not exist in your data:    IF(NVL(str1,'X') != NVL(str2,'Y')) THEN     dbms_output.put_line('The two strings are not equal');    END IF;  END;  / 

---

Concerning null comparisons:

According to the Oracle 12c documentation on NULLS, null comparisons using IS NULL or IS NOT NULL do evaluate to TRUE or FALSE. However, all other comparisons evaluate to UNKNOWN, not FALSE. The documentation further states:

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

A reference table is provided by Oracle:

Condition       Value of A    Evaluation ---------------------------------------- a IS NULL       10            FALSE a IS NOT NULL   10            TRUE         a IS NULL       NULL          TRUE a IS NOT NULL   NULL          FALSE a = NULL        10            UNKNOWN a != NULL       10            UNKNOWN a = NULL        NULL          UNKNOWN a != NULL       NULL          UNKNOWN a = 10          NULL          UNKNOWN a != 10         NULL          UNKNOWN 

I also learned that we should not write PL/SQL assuming empty strings will always evaluate as NULL:

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.

Answer 2

Let's fill in the gaps in your code, by adding the other branches in the logic, and see what happens:

SQL> DECLARE   2   str1  varchar2(4000);   3   str2  varchar2(4000);   4  BEGIN   5     str1:='';   6     str2:='sdd';   7     IF(str1<>str2) THEN   8      dbms_output.put_line('The two strings is not equal');   9     ELSIF (str1=str2) THEN  10      dbms_output.put_line('The two strings are the same');  11     ELSE  12      dbms_output.put_line('Who knows?');  13     END IF;  14   END;  15  / Who knows?  PL/SQL procedure successfully completed.  SQL> 

So the two strings are neither the same nor are they not the same? Huh?

It comes down to this. Oracle treats an empty string as a NULL. If we attempt to compare a NULL and another string the outcome is not TRUE nor FALSE, it is NULL. This remains the case even if the other string is also a NULL.