String Comparisons in Oracle

oracle| Comparison | String in Oracle, an empty string is treated as NULL, and any value and NULL comparison results are null. As a result, there is an unexpected exception when comparing two strings. Take a look at the following examples:
DECLARE
I VARCHAR2 (ten): = NULL;
V VARCHAR2 (+): = ' ABC ';
BEGIN
IF (i = v) THEN
Dbms_output. Put_Line (' equal ');
ELSE
Dbms_output. Put_Line (' unequal ');
End IF;
End;
Please judge with your clever mind, what should the result be? It is easy to come to the result: ' Unequal '. Well, you're right. The result of running on Sqlplus is the same as you think. Then I change the procedure, you judge again:
DECLARE
I VARCHAR2 (ten): = NULL;
V VARCHAR2 (+): = ' ABC ';
BEGIN
IF (i <> v) THEN
Dbms_output. Put_Line (' unequal ');
ELSE
Dbms_output. Put_Line (' equal ');
End IF;
End;
It doesn't look much different from the first program, and it's easy to get the result: ' Unequal '. Oh. Are you sure this is the result? Then please test it in sqlplus to verify that you are correct. Unfortunately, the correct result should be: ' equal '. Are you surprised? As is said at the beginning: any value and null comparison result is null. That is, the result of a i=v comparison in the first program should be null, and the result of a i<>v comparison in the second program is null. When the condition in the if structure is null, the current branch is skipped into else or end. Can't you? Then you run the following program will be able to wait until the validation:
BEGIN
IF (NULL) THEN
Dbms_output. Put_Line (' not NULL ');
ELSE
Dbms_output. Put_Line (' NULL ');
End IF;
End;
The result output is: ' NULL '.
So how do you compare two strings correctly?
First I'll say how to determine whether two strings are equal (or with the top two variables I and V).
1, when I and V are null, I and V are considered equal. I am null and V is null (do not write this: i = v.) From the above analysis we can know that the result of this writing is null.
2, when I and V only one is null, certainly not equal.
3, when I and V are not NULL, we can use ' = ' to determine whether they are equal. I am not null and V are NOT null and I = v.
Based on the above three points, we can conclude that the conditional expressions of I and V are equal: I is null and V is null OR I am NOT null and i = v.
Then the two-string unequal conditional expression only needs to judge that the equality expression is false.
Write a function that determines whether two strings are equal:
CREATE OR REPLACE FUNCTION isequal
(
VAR1 in VARCHAR2,
VAR2 in VARCHAR2
)
Return number--0: Unequal 1: Equal-1: Error
Is
IF (VAR1 is null and VAR2 is null OR VAR1 isn't null and VAR2 is not null and VAR1 = VAR2) THEN
return 1;
ELSE
return 0;
ENF IF;
BEGIN
EXCEPTION
When others THEN
RETURN-1;
End;
The following test program:
DECLARE
VAR1 VARCHAR2 (a): = NULL;
VAR2 VARCHAR2 (A): = ' A ';
BEGIN
IF (IsEqual (VAR1, VAR2) = 1) THEN
Dbms_output. Put_Line (' = ');
ELSE
Dbms_output. Put_Line (' <> ');
End IF;
End;