Oracle PL/SQL/PL SQL/Char Varchar Compare

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

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>

Comparison of a fixed length string and a literal

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>

Constants are compared using blank-padded comparison semantics

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>

-Fixed length strings are also compared with blank-padded

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>

The maximum lengths of varchar2 strings do not matter

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>