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?
This question is tagged with
~ Asked on 2011-09-02 15:52:57
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
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
FALSE. However, all other comparisons evaluate to
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.
~ Answered on 2011-09-02 16:07:43
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.
~ Answered on 2011-09-05 15:12:53