Char Varchar2 test

A simple Char versus Varchar2 comparison.

Create table test_char(abcb char(1),abcv varchar2(1));

Insert into hr.test_char values('a','b');
Commit;

SQL> Select * from hr.test_char where abcb='a';

A A
- -
a b

SQL> select * from hr.test_char where abcb='a ';

A A
- -
a b

SQL> Select * from hr.test_char where abcv='b';

A A
- -
a b

SQL> Select * from hr.test_char where abcv='b ';

no rows selected

Now! Lets dump the columns and see what we get.
SQL> Select dump(abcb)abcb,dump(abcv)abcv from hr.test_char;

ABCB                                        ABCV
--------------------------------------------------------------------------------
Typ=96 Len=1: 97                         Typ=1 Len=1: 98


Oracle Kernal considers CHAR datatype as fixed length. Any extra ' ' space added in
WHERE clause is trimmed.
However with VARCHAR2 this is not true. Varchar2 datatype is a variable length data type.
So when we add space in ABCV column Oracle tries to find equivalent value in table and return "No Rows Selected" message
because it has not found any row matching the values 'b '.


SQL> select * from hr.test_char where abcb='a ';  <-- ABCB is fixed length Char datatype. Oracle kernal implicitly does TRIMMING of extra
                          <-- space added in WHERE clause.

A A
- -
a b

SQL> Select * from hr.test_char where abcv = Trim('b '); <-- Now if we use TRIM function than we get desired result.

A A
- -
a b

SQL> Select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production



Any comment/suggestion would be appriciated.


Thanks