Posted by We all know that comparing two VARCHAR2 fields of different lengths will be treated by Oracle as being not EQUAL:
SQL> drop table T purge;
Table dropped.
SQL> create table T ( x varchar2(10), y varchar2(10));
Table created.
SQL> insert into T values ('abc','abc ');
1 row created.
SQL> select * from T where x = y;
no rows selected
SQL> select * from T where y = 'abc';
no rows selected
But interestingly enough, if there are no references to tables and/or columns, that is, you are just using plain literals, VARCHAR2 is not the datatype that is in play. For example:
SQL> select * from dual where 'abc' = 'abc ';
D
-
X
SQL> set serverout on
SQL> exec begin if 'abc' = 'abc ' then dbms_output.put_line('YES'); end if; end;
YES
PL/SQL procedure successfully completed.
You can see this from the DUMP command, type=1 is VARCHAR2, and type=96 is CHAR.
SQL> select dump(x) from t;
DUMP(X)
-------------------------------
Typ=1 Len=3: 97,98,99
SQL> select dump('abc') from dual;
DUMP('ABC')
----------------------
Typ=96 Len=3: 97,98,99
Learning is not a spectator sport 
To quote from the documentation, https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF51037, “Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.”
That sounds all very clear, however, I still get some odd behaviour.
On 12.1.0.2, equal but not equal:
SQL> select case when ‘abc’ = ‘abc ‘ then ‘Y’ else ‘N’ end equal
from dual where ‘abc’ = ‘abc ‘ ;
E
–
N
1 row selected
On 11.2.0.4, equal but maybe not:
SQL> select case when x = y then ‘Y’ else ‘N’ end equal
from ( select ‘abc’ x, ‘abc ‘ y from dual )
E
–
Y
1 row selected.
SQL> select case when x = y then ‘Y’ else ‘N’ end equal
, case when substr(‘abc’,1) || x = y then ‘y’ else ‘N’ end not_anymore
from ( select ‘abc’ x, ‘abc ‘ y from dual )
E N
– –
N N
1 row selected.
dump seems to be see the difference between literals
SQL> select dump(‘abc’) from dual;
DUMP(‘ABC’)
———————-
Typ=96 Len=3: 97,98,99
SQL> select dump(‘abc ‘) from dual;
DUMP(‘ABC’)
—————————-
Typ=96 Len=5: 97,98,99,32,32