SQL CHAR

What does the SQL CHAR keyword do?

CHAR defines a fixed-length character string or converts ASCII codes to characters, depending on the SQL dialect.

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 CHAR: Supported as data type in PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift. CHAR() function available in MySQL, MariaDB, SQL Server, SQLite; use CHR() in PostgreSQL and Oracle.

SQL CHAR Full Explanation

In standard SQL, CHAR (also written as CHARACTER) is a fixed-length string data type. When you declare a column as CHAR(N), every stored value is padded on the right with spaces until it reaches exactly N characters. This predictable storage size can speed up certain index and memory operations compared with variable-length VARCHAR fields, especially for short, uniform codes such as country or status indicators.Some dialects (MySQL, SQL Server, MariaDB) also expose a CHAR( ) scalar function that returns the character corresponding to one or more numeric ASCII/Unicode code points. While the data-type and the function share a name, they serve different purposes.Key behaviors:- Length must be a positive integer. Omitting it defaults to 1 in most systems (CHAR equals CHAR(1)).- On insert or update, shorter strings are space-padded; longer strings raise an error or are truncated, depending on SQL mode.- Trailing spaces are considered part of the value but many comparisons ignore them, which can cause subtle bugs.- Collation and character-set rules apply exactly as for VARCHAR.- Converting CHAR to VARCHAR generally removes trailing spaces; converting the other way adds them.Caveats:- Space padding increases storage when N is large or highly variable.- Equality tests against literals must include padding in some engines unless ANSI padding rules are active.- In Oracle, fixed-length semantics apply only to CHAR, while NCHAR is used for Unicode fixed-length data.- The CHAR( ) function is not available in PostgreSQL (use CHR) or SQLite (use CHAR()). Always verify the correct function name.

SQL CHAR Syntax

-- Data-type form
column_name CHAR[(length)]

-- Function form (dialect specific)
CHAR(numeric_code [, numeric_code...])

SQL CHAR Parameters

length (integer) - Required for the data type (default 1). Defines the exact number of characters to store.
numeric_code (integer) - One or more ASCII/Unicode code points to convert to characters when using the function form.

Example Queries Using SQL CHAR

-- 1. Using CHAR as a data type
CREATE TABLE countries (
    iso_code CHAR(2) PRIMARY KEY,
    name      VARCHAR(80)
);

INSERT INTO countries (iso_code, name)
VALUES ('US', 'United States');

SELECT iso_code, LENGTH(iso_code) AS len
FROM countries;

-- 2. Using CHAR() function in MySQL / SQL Server
SELECT CHAR(65)           AS single_char,  -- returns 'A'
       CHAR(65,66,67)     AS word;         -- returns 'ABC'

Expected Output Using SQL CHAR

The table is created with a fixed 2-character column. Selecting LENGTH returns 2 because 'US' is space-padded to 'US '.
The SELECT with CHAR() returns rows where single_char = 'A' and word = 'ABC'.

Use Cases with SQL CHAR

Store ISO country codes, state abbreviations, gender flags, or any field with uniform short length.
Guarantee consistent key width for indexing to improve look-ups.
Generate characters from ASCII codes in scripts (dialect supporting CHAR function).

Common Mistakes with SQL CHAR

Confusing CHAR with VARCHAR and unintentionally padding values.
Forgetting that comparisons may ignore or respect trailing spaces depending on settings.
Supplying a string longer than the declared length and trusting silent truncation.
Expecting the CHAR() function in databases that only support CHR() or different names.

First Introduced In

ANSI SQL-86

Frequently Asked Questions

What length should I choose for a CHAR column?

Pick the smallest length that still fits every possible value. Common examples: country codes (2), gender (1), and checksum letters (1).

Why do comparisons sometimes ignore trailing spaces?

SQL’s ANSI padding rules treat fixed-length strings of the same length as equal even if the right side is padded. Some engines trim spaces during comparison, making 'A' equal to 'A '.

Can I change a column from VARCHAR to CHAR safely?

Yes, but the engine will pad existing data. Always verify storage impact and update any code that assumes trimmed values.

Does CHAR support Unicode characters?

CHAR stores whatever the column’s character set supports. Declare CHAR in a Unicode-enabled database or use NCHAR to guarantee UTF-16 in SQL Server and Oracle.