VARCHAR2 Data Type

1. VARCHAR2 is used to store variable-length character strings.
2. When you define VARCHAR2(n), it can store up to n characters, but doesn’t pad extra spaces.
3. If you insert fewer characters, no extra spaces are added.
4. Maximum size of VARCHAR2 is 4000 bytes in SQL and up to 32767 bytes in PL/SQL.
5. VARCHAR2 is best for data that varies in length, like names, emails, or addresses.
6. Example: If you define VARCHAR2(10) and insert "ABC", Oracle stores it as "ABC" (no extra spaces).
7. It saves storage space compared to CHAR for variable-length data.
8. Comparisons consider the exact value (trailing spaces are not added).
9. VARCHAR2 may have slightly higher processing overhead than CHAR for fixed-length data.
10. It is defined by specifying the column name and maximum length, for example, a column of length 50.

 Example:

1.     VARCHAR2(5) → Input: A → Stored as: A

2.     VARCHAR2(5) → Input: AB → Stored as: AB

3.     VARCHAR2(5) → Input: #@! → Stored as: #@!

4.     VARCHAR2(4) → Input: क (Hindi) → Stored as: क

5.     VARCHAR2(6) → Input: 123 → Stored as: 123

6.     VARCHAR2(3) → Input: 😊 → Stored as: 😊

7.     VARCHAR2(8) → Input: مرحبا (Arabic) → Stored as: مرحبا

8.     VARCHAR2(10) → Input: HELLO → Stored as: HELLO

9.     VARCHAR2(4) → Input: π → Stored as: π

10.  VARCHAR2(7) → Input: テスト (Japanese) → Stored as: テスト

 

Key differences from CHAR:

·        VARCHAR2 does not add blank spaces.

·        Works for letters, numbers, symbols, emojis, and other languages.

·        Storage depends on actual length of the input.

If you want, I can also make a ready-to-use INSERT list for these 10 examples.

 

CHAR Data Type

1.     CHAR is used to store fixed-length character strings.

2.     When you define CHAR with a length, it always stores exactly that many characters.

3.     If you insert fewer characters, Oracle adds spaces to fill the remaining length.

4.     Maximum size of CHAR is 2000 bytes.

5.     CHAR is best for data that has a constant length, like gender codes or country codes.

6.     For example, if you define CHAR with length 5 and insert "ABC", Oracle will store it as "ABC ".

7.     It uses blank-padding, not variable storage.

8.     Comparisons ignore trailing spaces.

9.     CHAR uses less processing overhead than VARCHAR2.

10.  It is defined by specifying the column name and length, for example, a column of length 10.

 

Examples 

1.     CHAR(5) → Input: ABCDE → Stored as: 'ABCDE' ✅ (no extra spaces)

2.     CHAR(5) → Input: AB → Stored as: 'AB '

3.     CHAR(3) → Input: 😊 → Stored as: '😊 '

4.     CHAR(4) → Input: π → Stored as: 'π '

5.     CHAR(6) → Input: 123456 → Stored as: '123456' ✅ (no extra spaces)

6.     CHAR(8) → Input: HELLO → Stored as: 'HELLO '

7.     CHAR(4) → Input: IN → Stored as: 'IN '

8.     CHAR(7) → Input: テスト → Stored as: 'テスト '

9.     CHAR(2) → Input: Y → Stored as: 'Y '

10.  CHAR(5) → Input: #@!$% → Stored as: '#@!$%' ✅ (no extra spaces)

Key idea:

·        If the input length = CHAR(n) → no blank spaces added.

·        If input length < CHAR(n) → Oracle pads with blanks on the right.