Difference between CHAR, NCHAR, VARCHAR, VARCHAR2, and NVARCHAR in Oracle SQL (Basic 1 - 19)

CHAR, NCHAR, VARCHAR, VARCHAR2, and NVARCHAR are all string data types in Oracle SQL. However, there are some key differences between them.

CHAR and NCHAR

CHAR and NCHAR are fixed-length string data types. This means that they will always reserve the specified amount of storage space, even if the string is shorter. CHAR stores characters in the database character set, while NCHAR stores characters in the national character set.

VARCHAR and VARCHAR2

VARCHAR and VARCHAR2 are variable-length string data types. This means that they will only reserve the amount of storage space that is needed to store the string. VARCHAR stores characters in the database character set, while VARCHAR2 stores characters in the national character set.

NVARCHAR

NVARCHAR is a variable-length string data type that stores characters in the national character set. It is similar to VARCHAR2, but it can store Unicode characters.

Other differences

• VARCHAR and VARCHAR2 can store up to 2000 bytes of characters, while NVARCHAR can store up to 4000 bytes of characters.
• VARCHAR can store NULL values, but VARCHAR2 cannot.
• VARCHAR and VARCHAR2 can be used in all versions of Oracle SQL, but NVARCHAR is only available in Oracle 8i and later versions.

Example

The following example shows how to create a table with each of the string data types:

SQL

CREATE TABLE table_name (
  char_col CHAR(10),
  nchar_col NCHAR(10),
  varchar_col VARCHAR(10),
  varchar2_col VARCHAR2(10),
  nvarchar_col NVARCHAR(10)
);

Which data type should you use?

The best data type to use depends on your specific needs. If you need to store a fixed length string, then use CHAR or NCHAR. If you need to store a variable length string, then use VARCHAR or VARCHAR2. If you need to store Unicode characters, then use NVARCHAR.

Conclusion

CHAR, NCHAR, VARCHAR, VARCHAR2, and NVARCHAR are all string data types in Oracle SQL. The best data type to use depends on your specific needs.