top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is the difference between CHAR, VARCHAR, VARCHAR2 data types?

+3 votes
510 views
What is the difference between CHAR, VARCHAR, VARCHAR2 data types?
posted Jul 15, 2015 by Archana

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

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

answer Jul 15, 2015 by Manikandan J
...