The Difference between CHAR, VARCHAR and VARCHAR2

Oracle has three datatypes to store strings. – CHAR, VARCHAR and VARCHAR2. How do they differ from each other? When should you use which?

Let’s find out the answers in this article.

The simplest thing out of the way first: VARCHAR and VARCHAR2 are the same. But that this today – this might change. Have a look at Oracle documentation 10G Release 2:

Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics.

In this article, VARCHAR2 is used to mean both VARCHAR and VARCHAR2 as they are equivalent today.

How are CHAR and VARCHAR2 different then?

The difference is that CHAR(n) will ALWAYS be n bytes long. If the string length is <n, it will be blank padded upon insert to ensure a length of n. A VARCHAR2(n) on the other hand will be 1 to n bytes long. A shorter string stored as VARCHAR2 will NOT be blank padded.

For example, suppose you store the string ‘ORATABLE’ in a CHAR(20) field and a VARCHAR2(20) field. The CHAR field will use 22 bytes (2 bytes for leading length). The VARCHAR2 field will use 10 bytes only (8 for the string, 2 bytes for leading length).

To summarize, CHAR is VARCHAR2 padded to its maximum length.

How does this difference impact SQLs?

This changes the way strings are matched in CHAR vs VARCHAR2. Check it out in action:

SQL> create table strings
  2   (colchar char (20)
  3  , colvarchar varchar2 (20));

Table created.

SQL>
SQL> insert into strings
  2   (colchar, colvarchar)
  3  values
  4   ('ORATABLE', 'ORATABLE');

1 row created.

SQL> -- Define a string variable
SQL> var str varchar2(20)
SQL> -- Give it a value with length < 20
SQL> exec :str := 'ORATABLE'

PL/SQL procedure successfully completed.

SQL> -- Exact string match with CHAR
SQL> -- No result found!
SQL> select 'found' found_flag
  2  from   strings
  3  where  colchar = :str;

no rows selected

SQL> -- Padded string match with CHAR
SQL> -- Now it finds it
SQL> select 'found' found_flag
  2  from   strings
  3  where  colchar = rpad(:str,20);

FOUND
-----
found

So you see, padding with blanks makes a difference to whether the CHAR string gets matched with a variable length parameter. To get a match, you need to either rpad the value or rtrim the column.

No such requirement with VARCHAR2:

SQL> -- Exact string match with VARCHAR2
SQL> -- Result found!
SQL> select 'found' found_flag
  2  from   strings
  3  where  colvarchar = :str;

FOUND
-----
found

When should we use CHAR, when VARCHAR2?

If you only ever use VARCHAR2 and ignore CHAR, you will make life much simpler. There is no difference between the two except that CHAR uses up more space when your strings are not always of the fixed maximum length. Plus, CHAR leads to more confusion in writing queries.

I use CHAR as database columns only for Y/N type of values. (There is no BOOLEAN column datatype, remember?) This acts like a marker for a “flag” or “switch” type of column – but it could equally well have been VARCHAR2.

For all other strings, it’s VARCHAR2.

Summary

When considering the Oracle datatypes CHAR, VARCHAR and VARCHAR2, remember that:

• VARCHAR and VARCHAR2 are the same, but the function of VARCHAR might change in future. Use VARCHAR2.
• CHAR and VARCHAR2 are different in that CHAR blank pads its strings to be of its maximum length. VARCHAR2 does no blank padding.
• Because of the above, queries that involve string comparison on a CHAR column require more care – a VARCHAR2 compared to CHAR needs to be rpad-ded to get a hit.
• It bears repeating: use VARCHAR2!