Differences Between Fixed-Length (CHAR) and Variable-Length (VARCHAR) Strings and a Practical Examination

When storing string data in a database, developers often face the decision of whether to use the CHAR or VARCHAR data type. While both are used to define string-type columns, they differ significantly in internal behavior, use cases, and performance characteristics.

This article systematically outlines the features of each type and provides clear guidance on when to use them. Furthermore, we perform a practical test using the CHAR_LENGTH() function to illustrate how trailing spaces are handled differently. The goal is to help developers make the right choice during schema design.

---

1. Basic Differences Between CHAR and VARCHAR

In database schema design, string-type columns are defined according to application requirements. CHAR and VARCHAR are two of the most commonly used types. Below is an overview of their respective characteristics.

1.1 CHAR Type (Fixed-Length String)

The CHAR type defines a fixed-length string. If a string shorter than the defined length is stored, the remaining space is automatically padded with spaces, and the total length is always consistent.

Example: CHAR(10) column definition

CREATE TABLE test_char (
  id INT,
  name CHAR(10)
);

In this case, the name column can store up to 10 characters. If the string “John” (4 characters) is stored, it will be saved as "John " (with six trailing spaces), using the full 10-character space.

This fixed-length storage allows for faster searching and comparison, offering performance benefits. However, the downside is increased storage usage, as even short strings consume the full allocated length.

---

1.2 VARCHAR Type (Variable-Length String)

The VARCHAR type is variable-length, meaning it only uses the amount of space required by the actual string. You must define a maximum character length, but only the characters that are stored will occupy space.

Example: VARCHAR(10) column definition

CREATE TABLE test_varchar (
  id INT,
  name VARCHAR(10)
);

If the string “John” is inserted, only 4 characters’ worth of space is used, making this a more storage-efficient option.

However, because the length varies per row, operations like indexing and comparison may incur a slight performance overhead compared to CHAR.

---

2. Comparison Table: CHAR vs. VARCHAR

Category	CHAR (Fixed-Length)	VARCHAR (Variable-Length)
Storage Length	Always fixed (padded with spaces)	Varies with the actual data length
Handling of Trailing Spaces	Trailing spaces are automatically removed	Trailing spaces are stored as part of the data
Storage Usage	Always uses the full defined length	Uses only the required length
Search & Comparison Speed	High (fixed length improves performance)	Slightly slower due to variable length
Suitable Use Cases	Phone numbers, ZIP codes, employee IDs, bank codes	Addresses, comments, notes, free-form text messages

---

3. Practical Test: Handling of Trailing Spaces (Using CHAR_LENGTH)

One of the most notable differences between CHAR and VARCHAR is how trailing spaces are treated.
In this section, we use the CHAR_LENGTH() function to observe the actual behavior when trailing spaces are included in the stored strings.

3.1 Table Definitions

CREATE TABLE test_char (
  id INT,
  name CHAR(10)
);

CREATE TABLE test_varchar (
  id INT,
  name VARCHAR(10)
);

3.2 Insert Data With Trailing Spaces

INSERT INTO test_char VALUES (2, 'John  ');
INSERT INTO test_varchar VALUES (2, 'John  ');

3.3 Check Character Lengths

SELECT id, name, CHAR_LENGTH(name) AS char_len FROM test_char;
SELECT id, name, CHAR_LENGTH(name) AS char_len FROM test_varchar;

3.4 Expected Results and Interpretation

Table	name	CHAR_LENGTH(name)
test_char	John	4
test_varchar	John␣␣	6

␣ represents a space character.

Explanation

• For the CHAR type, trailing spaces are treated as if they are removed, so CHAR_LENGTH() returns only the number of meaningful characters—in this case, 4.
• For the VARCHAR type, trailing spaces are preserved, so the result is 6.

This test clearly shows that the actual stored content differs depending on the data type, even if the same input string is used. This behavior can influence search and comparison operations.

---

4. Guidelines for Practical Use

There is no universal answer to whether CHAR or VARCHAR is better—the optimal choice depends on the nature of the data. Below are practical guidelines for choosing the right type.

When to Use CHAR

• The data length is fixed or nearly consistent.
• Performance in searches and comparisons is a priority.
• Trailing spaces should be ignored or trimmed automatically.

Examples: phone numbers, bank codes, employee IDs, prefecture codes

When to Use VARCHAR

• The data length varies widely.
• Storage efficiency is important.
• Trailing spaces are meaningful and must be retained.

Examples: addresses, remarks, free-form text fields, email bodies

---

Conclusion

Understanding the differences between CHAR and VARCHAR is essential for effective database design. In this article, we compared their characteristics and performed a practical test focused on how trailing spaces are handled.

Rather than always defaulting to VARCHAR for string data, carefully consider the nature of the data: Is it uniform in length? Will it be used frequently in indexed searches? Are trailing spaces significant?

By answering such questions, you can make an informed decision that leads to a more efficient and maintainable database schema.