Difference Between Char,Varchar and Varchar2

The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of  the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot “find” their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed .

Here is Demo which will clear our doubt about the char and varchar .

1.) CHAR   :   Char should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.

SQL> create table char_test (col1 CHAR(10));
Table created.

SQL> insert into  char_test  values ('qwerty');
1 row created.                                                                                                                                            
COL1             LENGTH(COL1)           ASCII Dump
------               -----------                     ----------------------------------------------------------
qwerty             10                              Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32

2.) VARCHAR  :   Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.

SQL> create table  varchar_test (col1 varchar2(10));
Table created.

SQL> insert into varchar_test  values  ('qwerty');
1 row created.

SQL> select  col1, length(col1), dump(col1) "ASCII Dump"  from varchar_test;
COL1           LENGTH(COL1)            ASCII Dump
-------          ------------                        --------------------------------------------------
qwerty            6                                   Typ=1 Len=6: 113,119,101,114,116,121

3.) VARCHAR2  :   Varchar2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.

SQL> create table  varchar2_test (col1 varchar2(10));
Table created.

SQL> insert into varchar2_test values ('qwerty');
1 row created.

SQL>  select  col1, length(col1), dump(col1) "ASCII Dump"  from  varchar2_test;
COL1                LENGTH(COL1)                ASCII Dump
----------           ------------------                     ------------------------------------------
qwerty                     6                                  Typ=1 Len=6: 113,119,101,114,116,121


Below is an another example of  "char"  which will help us to understand the concept  :

SQL> select * from char_test where col1 = 'qwerty';
COL1
----------
qwerty

SQL> variable y varchar2(25);
SQL> exec :y := 'qwerty'
PL/SQL procedure successfully completed.

SQL> select * from char_test where col1 = :y;
no rows selected

SQL> select * from char_test where col1 = rpad(:y,10);
COL1
----------
qwerty

Notice how when doing the search with a varchar2 variable (almost every tool in the world uses this type), we have to rpad() it to get a hit. If the field is in fact always 10 bytes long, using a char will not hurt -- However, it will not help either.

The only time I personally use a CHAR type is for CHAR(1).  And that is only because its faster to type char(1) then varchar2(1) --  it offers no advantages.(according to t kytes).


Enjoy     :-)