SQL CHARACTER

What is the SQL CHARACTER data type?

CHARACTER (or CHAR) declares a fixed-length string data type in SQL.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CHARACTER: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift

SQL CHARACTER Full Explanation

CHARACTER is a standard SQL data type keyword that stores fixed-length character strings. When you declare a column or cast a value as CHARACTER(n) or CHAR(n), the database always reserves n bytes or characters, padding shorter strings with spaces on the right to reach the defined length. This predictable storage size can improve performance for small, uniformly sized codes such as country ISO codes or status flags. However, the padding means that trailing spaces are usually ignored in comparisons, and storing longer text wastes space. CHARACTER without a length defaults to 1 in most systems, but some dialects require the length explicitly. Because the value is padded on write, the stored size never shrinks until the column is updated.

SQL CHARACTER Syntax

CHARACTER(n)
CHAR(n)
-- Casting
CAST(value AS CHARACTER(n))

SQL CHARACTER Parameters

n (integer) - Required maximum length in characters or bytes, depending on the database encoding.

Example Queries Using SQL CHARACTER

-- Table with a fixed 2-letter country code
CREATE TABLE countries (
  code CHARACTER(2) PRIMARY KEY,
  name VARCHAR(100)
);

-- Insert shorter string; database pads to 2 characters
INSERT INTO countries (code, name) VALUES ('U', 'Sample');

-- Cast a literal to CHAR(5)
SELECT CAST('abc' AS CHARACTER(5)) AS padded_text;

Expected Output Using SQL CHARACTER

The table is created
The inserted value 'U' is stored as 'U ' (U plus one space)
The SELECT returns 'abc ' (abc plus two spaces)

Use Cases with SQL CHARACTER

Storing fixed-size codes such as state abbreviations, ISO country codes, or yes/no flags
Improving index performance when every value is the same width
Ensuring legacy applications that expect fixed-width files receive consistent padding

Common Mistakes with SQL CHARACTER

Forgetting that CHARACTER pads with spaces and wondering why text appears longer when retrieved
Declaring large n values and wasting storage instead of using VARCHAR
Assuming CHARACTER trims trailing spaces automatically when concatenating
Omitting n and expecting more than one character to be stored

First Introduced In

SQL-86 standard

Frequently Asked Questions

What happens if I insert a shorter string than the declared length?

The database right-pads the value with spaces up to the defined length. When you retrieve the value the spaces are still stored, although many client tools trim them when displaying.

Is CHARACTER more performant than VARCHAR?

For small, uniform codes CHAR can improve indexing and storage predictability. For general text VARCHAR is usually better because it avoids wasting space.

How do I remove trailing spaces from a CHAR column?

Use TRIM or RTRIM functions: `SELECT TRIM(trailing FROM col) FROM table_name;` This strips the padding characters.

Can I change a column from CHARACTER to VARCHAR later?

Yes. Use `ALTER TABLE table_name ALTER COLUMN col TYPE VARCHAR(n);` (syntax varies by dialect). The database rewrites the table to remove padding.