Oracle string comparison --- null

Oracle string comparison --- null in ORACLE, the NULL String is treated as NULL, and the comparison result of any value and NULL is null. In this way, an exception occurs when comparing two strings. See the following example: DECLARE I VARCHAR2 (10): = NULL; v VARCHAR2 (10): = 'abc'; begin if (I = v) THEN DBMS_OUTPUT.PUT_LINE ('equal '); ELSE DBMS_OUTPUT.PUT_LINE ('unequal'); end if; END; please use your smart mind to determine what the result should be? It is easy to get the result: 'unequal '. Good. You're right. The running result on SQLPLUS is the same as what you want. Then I will change the program and you will judge: DECLARE I VARCHAR2 (10): = NULL; v VARCHAR2 (10): = 'abc'; begin if (I <> v) THEN DBMS_OUTPUT.PUT_LINE ('unequal '); ELSE DBMS_OUTPUT.PUT_LINE ('equal'); end if; END; it seems that there is not much difference with the first program, so it is easy to get the result: 'unequal '. Haha. Are you sure this is the result? Test SQL plus to verify that you are correct. Unfortunately, the correct result should be: 'Equality '. Are you surprised? As mentioned at the beginning: the result of comparing any value with NULL is NULL. That is, the result of I = v comparison in the first program should be NULL, and the result of I <> v comparison in the second program is also NULL. When the condition in the IF structure is NULL, the current branch will be skipped and entered to ELSE or ended. No? THEN you can run the following program for verification: begin if (NULL) THEN DBMS_OUTPUT.PUT_LINE ('not null'); ELSE DBMS_OUTPUT.PUT_LINE ('null'); end if; END; the result is 'null '. So how should we correctly compare the two strings? First, let's talk about how to determine whether the two strings are equal (or use the two variables I and v above ). 1. When I and v are both NULL, I and v are considered equal. I is null and v is null (do not write it like this: I = v. From the above analysis, we can know that the Write result is NULL ). 2. When only one of I and v is NULL, it is definitely not equal. 3. When neither I nor v is NULL, we can use '=' to determine whether they are equal. I is not null and v is not null and I = v. Based on the above three points, we can obtain the condition expression that IS equal to I AND v: I is null and v is null or I is not null and v is not null and I = v. If the two strings are not equal, you only need to judge the equal expression as FALSE. Write the function to determine whether two strings are equal as a function: create or replace function isequal (var1 in varchar2, var2 in varchar2) return number -- 0: unequal 1: Equal-1: error isbegin if (var1 is null and var2 is null or var1 is not null and var2 is not null and var1 = var2) then return 1; else return 0; end if; exception when others then return-1; end;