Oracle PL/SQL/PL SQL/Char Varchar Compare

Compare a variable length string with a fixed length, and the trailing spaces do matter

   <source lang="sql">

SQL> SQL> -- Demonstration of string comparison semantics. SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    fixed_length_10  CHAR(10);
 3    fixed_length_20  CHAR(20);
 4    var_length_10    VARCHAR2(10);
 5    var_length_20    VARCHAR2(20);
 6  BEGIN
 7
 8    -- Compare a variable length string
 9    -- against a fixed length, and the
10    -- trailing spaces do matter.
11    var_length_10 := "Donna";
12    IF fixed_length_10 = var_length_10 THEN
13      DBMS_OUTPUT.PUT_LINE("Char and Varchar2:"
14                            || fixed_length_10 ||" = "
15                           || var_length_10 );
16    ELSE
17      DBMS_OUTPUT.PUT_LINE("Char and Varchar2: "
18                            || fixed_length_10 ||" NOT = "
19                            || var_length_10);
20    END IF;
21
22  END;
23  /

Char and Varchar2: NOT = Donna PL/SQL procedure successfully completed. SQL>

      </source>
   
  

Comparison of a fixed length string and a literal

   <source lang="sql">

SQL> SQL> -- Demonstration of string comparison. SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    fixed_length_10  CHAR(10);
 3    fixed_length_20  CHAR(20);
 4    var_length_10    VARCHAR2(10);
 5    var_length_20    VARCHAR2(20);
 6  BEGIN
 7
 8    --Comparison of a fixed length string and a literal also
 9    -- results in the use of blank-padded comparison semantics.
10    IF fixed_length_10 = "Donna" THEN
11       DBMS_OUTPUT.PUT_LINE("Char and constant:" || fixed_length_10 ||" = " || "Donna");
12    ELSE
13       DBMS_OUTPUT.PUT_LINE("Not Donna");
14    END IF;
15  END;
16  /

Not Donna PL/SQL procedure successfully completed. SQL>

      </source>
   
  

Constants are compared using blank-padded comparison semantics

   <source lang="sql">

SQL> SQL> -- Demonstration of string comparison. SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    fixed_length_10  CHAR(10);
 3    fixed_length_20  CHAR(20);
 4    var_length_10    VARCHAR2(10);
 5    var_length_20    VARCHAR2(20);
 6  BEGIN
 7    --Constants are compared using blank-padded comparison semantics,
 8    -- so the trailing spaces won"t affect the result.
 9    IF "Abc" = "Abc          " THEN
10       DBMS_OUTPUT.PUT_LINE("Constant: ""Abc"" = ""Abc          """);
11    END IF;
12
13  END;
14  /

Constant: "Abc" = "Abc " PL/SQL procedure successfully completed. SQL>

      </source>
   
  

-Fixed length strings are also compared with blank-padded

   <source lang="sql">

SQL> SQL> --Demonstration of string comparison semantics. SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    fixed_length_10  CHAR(10);
 3    fixed_length_20  CHAR(20);
 4  BEGIN
 5    --Fixed length strings are also compared with blank-padded
 6    -- comparison semantic, so the fact that one is longer doesn"t matter.
 7    fixed_length_10 := "Donna";
 8    fixed_length_20 := "Donna";
 9    IF fixed_length_20 = fixed_length_10 THEN
10       DBMS_OUTPUT.PUT_LINE("Char: " || fixed_length_10 || " = " || fixed_length_20);
11    END IF;
12
13  END;
14  /

Char: Donna = Donna PL/SQL procedure successfully completed. SQL>

      </source>
   
  

The maximum lengths of varchar2 strings do not matter

   <source lang="sql">

SQL> SQL> -- Demonstration of string comparison semantics. SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    fixed_length_10  CHAR(10);
 3    fixed_length_20  CHAR(20);
 4    var_length_10    VARCHAR2(10);
 5    var_length_20    VARCHAR2(20);
 6  BEGIN
 7     --the maximum lengths of varchar2 strings do not matter,
 8     --only the assigned values.
 9     var_length_10 := "Donna";
10     var_length_20 := "Donna";
11     IF var_length_20 = var_length_10 THEN
12       DBMS_OUTPUT.PUT_LINE("Both Varchar2:" || var_length_20 ||" = " || var_length_10);
13     ELSE
14       DBMS_OUTPUT.PUT_LINE("Both Varchar2:" || var_length_20 ||" NOT = " || var_length_10);
15     END IF;
16  END;
17  /

Both Varchar2:Donna = Donna PL/SQL procedure successfully completed. SQL>

      </source>