Char vs varchar


Ever thought that comparing two fields of different lengths would necessarily be treated by Oracle as being not EQUAL? Think again - check out this demo

SQL> select * from dual where 'abc' = 'abc  '
  2  /
 
D
-
X
 
SQL> select length('abc') from dual;
 
LENGTH('ABC')
-------------
            3
 
SQL> select length('abc  ') from dual;
 
LENGTH('ABC')
-------------
            5

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


The issue here is the CHAR fields are compared by blank padding. Something you need to take care of when using CHAR instead of VARCHAR2.