SQL NCHAR

What is the SQL NCHAR data type?

NCHAR is a fixed-length Unicode (national character) string data type.

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 NCHAR: SQL Server, Oracle, MySQL, MariaDB, IBM Db2, SAP HANA, Teradata. PostgreSQL lacks true NCHAR but treats it as CHAR alias when standard_conforming_strings is on. SQLite parses NCHAR as TEXT affinity.

SQL NCHAR Full Explanation

NCHAR stores Unicode text using a fixed number of characters defined at declaration time (N). Every value occupies the full length, padded with trailing spaces if shorter than N. Because it is Unicode, each character typically uses two bytes (or more in UTF-16/UTF-32 implementations), guaranteeing support for multilingual data. Unlike NVARCHAR, which is variable-length, NCHAR always reserves the specified space, making it predictable in storage but potentially wasteful if data lengths vary widely.Key points:- Part of the SQL standard as the National character fixed-length type.- Ideal for columns where all values share the same length (country codes, ISO abbreviations).- Collation and comparison use the database’s national character set, often UTF-16.- Trailing spaces are considered semantically insignificant in comparisons in most systems.- Maximum length varies by dialect (e.g., SQL Server: 4,000, Oracle: 2,000, MySQL: 65,535 bytes total row size limit).- Requires more storage than CHAR when the database default character set is single-byte.- Cannot store strings longer than the declared length; excess data raises an error or is truncated, depending on SQL mode.

SQL NCHAR Syntax

NCHAR(n)

SQL NCHAR Parameters

n (INTEGER) - Number of Unicode characters to allocate (mandatory, dialect limits apply)

Example Queries Using SQL NCHAR

-- Create a table using NCHAR for fixed 2-character country codes
CREATE TABLE locations (
  id INT PRIMARY KEY,
  country_code NCHAR(2),
  city_name NVARCHAR(80)
);

-- Insert values (country_code will always occupy 2 chars)
INSERT INTO locations (id, country_code, city_name)
VALUES (1, 'US', 'New York'),
       (2, 'JP', 'Tokyo');

-- Selecting padded values
SELECT id, country_code, LENGTH(country_code) AS len
FROM locations;

Expected Output Using SQL NCHAR

Table is created, two rows inserted
Query returns: id | country_code | len----+--------------+----- 1 | 'US' | 2 2 | 'JP' | 2Trailing spaces are not visible but are stored to reach the fixed length

Use Cases with SQL NCHAR

Storing ISO country, state, or currency codes that are always the same length.
Maintaining legacy schemas that require fixed-width national character columns.
Ensuring uniform storage size for hash partitioning or predictable disk usage.

Common Mistakes with SQL NCHAR

Confusing NCHAR with NVARCHAR and assuming variable length.
Forgetting that trailing spaces are added, leading to unexpected results when concatenating.
Declaring large N values and wasting storage on mostly short strings.
Assuming NCHAR automatically strips trailing spaces on retrieval (they remain unless trimmed).

First Introduced In

SQL-92 (National Character Types)

Frequently Asked Questions

When should I use NCHAR rather than NVARCHAR?

Choose NCHAR when every stored value has an identical length, like ISO country codes. NVARCHAR is a better choice for variable-length text because it only allocates the space actually used.

How many bytes does NCHAR use per character?

Most engines store NCHAR in UTF-16, allocating 2 bytes per character. Some use UTF-32 for supplementary characters, raising storage to 4 bytes per character.

Does NCHAR automatically trim trailing spaces on retrieval?

No. The database stores and returns the full fixed length including spaces. Use TRIM or RTRIM functions to remove them if needed.

What happens if I insert a longer string than the declared N?

The statement fails with an error in strict modes, or the string is silently truncated depending on the database’s SQL mode and settings.