Difference Between CHAR, VARCHAR, and VARCHAR2 Data Types in Oracle (2024)

Oracle has several character data types that are commonly used and can also be confusing if you don’t know the differences between them. Learn what they are and the differences between them in this article.

The Oracle character data types that are used most often are CHAR and VARCHAR2. There is also a VARCHAR data type.

Let’s see what each of these data types are.

CHAR Datatype

The CHAR data type stores character values. It stores these values as a fixed-length string.

This means that string values will be padded, or have space characters added to them, to be stored in the database.

The maximum length of a CHAR value is 2000 bytes.

Let’s see an example of this.

I’ll create a sample table using all three of these data types, insert some data, and see what it shows.

CREATE TABLE chartest ( char_column CHAR(10), varchar_column VARCHAR(10), varchar2_column VARCHAR2(10));

Now, let’s insert a value.

INSERT INTO chartest (char_column)VALUES ('abcde');

Now, let’s query the value. I’ll query the value, and the value inside brackets (to see where spaces are), check the length, and then perform a DUMP on it to see some stats.

SELECT char_column,'(' || char_column || ')' AS col_full,LENGTH(char_column) AS col_length,DUMP(char_column) AS col_dumpFROM chartest;

Result:

As you can see, the value of abcde has been padded with spaces when stored to bring it up to a length of 10. In the DUMP function output, you can see the codes for each character that is stored. Character 32 is a space.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

VARCHAR Datatype

The VARCHAR datatype is an ANSI standard data type, which is why it is included in Oracle.

However, at the moment, is the same as the VARCHAR2 data type.

It was included in Oracle to allow for a distinction between NULL and empty strings, which is what VARCHAR should do, according to the ANSI standard. However, in Oracle, it does not do that. It works like a VARCHAR2 where empty strings are treated as NULL values.

So, in Oracle, the difference between VARCHAR and VARCHAR2 is nothing. They are the same.

You should not use the VARCHAR data type as it is reserved for future Oracle functionality and it could change.

Let’s see an example. We’ll use the same table as earlier, and insert some data.

INSERT INTO chartest (varchar_column)VALUES ('fghij');

Now, let’s select this data.

SELECT varchar_column,'(' || varchar_column || ')' AS col_full,LENGTH(varchar_column) AS col_length,DUMP(varchar_column) AS col_dumpFROM chartest;

Result:

You can see that the value in brackets does not contain spaces, which is because VARCHAR2 and VARCHAR do not add spaces (which I’ll discuss in a moment). It is also a length of 5 characters.

VARCHAR2 Datatype

The VARCHAR2 data type is used to store variable length strings. This means that you define a maximum size, and the strings that you insert into the column will be stored as they are provided. No spaces will be added to the character, like the CHAR datatype.

The maximum length of a VARCHAR2 data type is 4000 bytes. However, this was raised to 32,767 bytes as one of the new features in Oracle 12c.

Let’s see an example.

INSERT INTO chartest (varchar2_column)VALUES ('klmno');

Now, let’s query the result.

SELECT varchar2_column,'(' || varchar2_column || ')' AS col_full,LENGTH(varchar2_column) AS col_length,DUMP(varchar2_column) AS col_dumpFROM chartest;

Result:

You can see the output is the same as the VARCHAR example. No spaces have been added, and the length is the same. The only difference is the ASCII code charactersbecause the input string was different.

Differences: CHAR vs VARCHAR vs VARCHAR2

Let’s take a look at the differences between these three data types.

• VARCHAR and VARCHAR2 are exactly the same. CHAR is different.
• CHAR has a maximum size of 2000 bytes, and VARCHAR/VARCHAR2 has a maximum size of 4000 bytes (or 32,767 in Oracle 12c)
• CHAR does not need a size specified and has a default of 1. A size needs to be specified with VARCHAR/VARCHAR2 columns.
• CHAR will pad spaces to the right of strings to match the length of the column, while VARCHAR/VARCHAR2 does not.

The performance of CHAR, VARCHAR, and VARCHAR2 is the same. There is no difference in performance with these data types. CHAR is not faster than VARCHAR2, and VARCHAR2 is not faster than CHAR.

Tom Kyte has a post on AskTom that explains the technical details of this. He also explains that a blank-padded VARCHAR2 is exactly the same as a CHAR value.

Think about this for a moment.

Blank padding a VARCHAR2 value is the same as a CHAR value.

The data types are almost identical.

So, which of these data types should you use?

As Tom Kyte has discussed on his forum and in his book (and I agree with), there is no reason to ever use the CHAR data type.VARCHAR2 can cover all situations where a CHAR might be considered – even a CHAR(1) which is the same as a VARCHAR2(1).

VARCHAR vs VARCHAR2

The difference between VARCHAR and VARCHAR2 in Oracle is that VARCHAR is an ANSI-standard data type that supports a distinction between NULL and empty strings. Oracle has not yet implemented this distinction, so at the moment, VARCHAR and VARCHAR2 are the same. It’s safer to use VARCHAR2 as you don’t want any code to break if Oracle changes VARCHAR in the future and your code uses VARCHAR.

Conclusion

In conclusion, VARCHAR is the same as VARCHAR2 for now. CHAR is slightly different. You should use VARCHAR2 in all cases.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: