Introduction to Oracle VARCHAR2 Data Type

In Oracle Database, VARCHAR2 is a data type used to store variable-length strings, with a maximum length of 4000 bytes. Unlike the CHAR type, data of VARCHAR2 type is compressed based on its actual length during storage, making it more space-efficient.

Syntax

When creating or modifying a table structure, the following syntax can be used to define a column of VARCHAR2 type:

CREATE TABLE table_name (
  column_name VARCHAR2(size) [CHARACTER SET charset] [COLLATE collation]
);

where size represents the maximum length of the column, which can be set between 1 and 4000. CHARACTER SET and COLLATE can be used to specify the character set and collation.

Use Cases

VARCHAR2 data type is typically used for storing variable-length text information such as user names, addresses, emails, etc. Due to its ability to save storage space, using VARCHAR2 data type can improve storage efficiency in scenarios where large amounts of text information need to be stored.

Examples

Below are two examples of using VARCHAR2 data type:

Example 1

Create a user information table users with username, email, and password fields:

CREATE TABLE users (
  username VARCHAR2(50),
  email VARCHAR2(100),
  password VARCHAR2(50)
);

Insert a record into the table:

INSERT INTO users (username, email, password)
VALUES ('John', '[email protected]', '123456');

Query all records from the table:

SELECT * FROM users;

Output:

USERNAME	EMAIL	PASSWORD
John	[email protected]	123456

Example 2

Create an articles table articles with title and content fields:

CREATE TABLE articles (
  title VARCHAR2(200),
  content VARCHAR2(4000)
);

Insert an article into the table:

INSERT INTO articles (title, content)
VALUES ('Oracle Database Introduction', 'Oracle Database is a relational database management system commonly used for data management and storage in enterprise applications.');