Character comparison of Oracle Stored Procedure

Method 1

Today, I want to compare two characters in the stored procedure, but I found that the expected results were not obtained. The main problem is that the comparison of any value and null results are null, I found two methods on the Internet for your reference.

In Oracle, the Null String is considered null, and any value compared with 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 ('Equality ');
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. So let me change it. Program You can judge again:
Declare
I varchar2 (10): = NULL;
V varchar2 (10): = 'abc ';
Begin
If (I <> V) then
Dbms_output.put_line ('unequal ');
Else
Dbms_output.put_line ('Equality ');
End if;
End;
It seems that there is no big difference with the first program, and it is easy to come to 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? Run the following program and wait for verification:
Begin
If (null) then
Dbms_output.put_line ('not null ');
Else
Dbms_output.put_line ('null ');
End if;
End;
The result is as follows: '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 of determining whether two strings are equal into a function:
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 is not 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;
Test the procedure as follows:
Declare
Var1 varchar2 (10): = NULL;
Var2 varchar2 (10): = 'a ';
Begin
If (isequal (var1, var2) = 1) then
Dbms_output.put_line ('= ');
Else
Dbms_output.put_line ('<> ');
End if;
End;

From: http://www.mscto.com/Oracle/301028605.html

 

 

Method 2,

The nvl method is used as follows:

Nvl (var1 ,'')! = Nvl (var2, '') to compare the differences between var1 and var2. If var1 is null, it is converted to" "null character, if it is not empty, it is still the original value. However, I personally tested that there is also a problem when converting it to a null character to compare two characters. Oracle considers the Null String as NUL, the result is still the same as that of null. Therefore, if the character is null, we recommend that you convert it into a "unique" character, the following is my personal comparison of the two characters for your reference.

Pre_remarkVarchar2 (10): = NULL;
 RemarksVarchar2 (10): = 'a ';

If nvl (pre_remark, '@ # $ % ^ &')! = Nvl (remarks, '@ # $ % ^ &') then
Dbms_output.put_line ('true ');

Else

Dbms_output.put_line ('false ');

End if;

The returned result is true.