What is the difference between VARCHAR, VARCHAR2 ?

Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently. The VARCHAR type should not be used:

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.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM char_test;

COL1       LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty               10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32

 

 

 Note: ASCII character 32 is a blank space.

 

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

 

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