Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Constraints and Data Types

SQL CHAR, VARCHAR and TEXT types

Reference for SQL text types: fixed-length CHAR, variable-length VARCHAR(n) and unlimited TEXT, how SQLite ignores lengths, padding, collation and dialect names.

SQL has three families of text types. CHAR(n) stores fixed-length strings, VARCHAR(n) stores strings up to a maximum length, and TEXT (or a similar name such as CLOB) stores long strings with no practical limit. Which one you pick affects storage, comparison rules, and whether over-long values are rejected or silently kept.

SQLite is the odd one out: it accepts all of these names but stores every one of them as TEXT, and it does not enforce the length in parentheses. Treat the length as documentation in SQLite and as a real rule everywhere else.

SQL
CREATE TABLE tags (
  code  CHAR(3),
  label VARCHAR(30),
  notes TEXT
);

Try it

SQL Length and storage of text values ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL SQLite does not enforce VARCHAR(n)

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
CHAR(n)
VARCHAR(n)
TEXT
Type Meaning
CHAR(n) Fixed length of n characters. Shorter values are padded with spaces in most databases.
VARCHAR(n) Variable length up to n characters. No padding.
TEXT Long text with no declared limit. Not part of the SQL standard (the standard name is CLOB), but available almost everywhere.

Returns: a column of that type. Comparison and sorting follow the column's collation, which controls case and accent sensitivity.

Dialect differences

Database Fixed Variable Long text Notes
SQLite CHAR(n) VARCHAR(n) TEXT All have TEXT affinity. Lengths are never enforced and nothing is padded.
PostgreSQL CHAR(n) VARCHAR(n) TEXT Over-long values raise an error. TEXT and VARCHAR perform the same. CHAR(n) pads with spaces.
MySQL CHAR(n) VARCHAR(n) TEXT, MEDIUMTEXT, LONGTEXT Length is counted in characters. In strict mode (the default) over-long values are errors. Default comparisons are case-insensitive.
SQL Server CHAR(n), NCHAR(n) VARCHAR(n), NVARCHAR(n) VARCHAR(MAX), NVARCHAR(MAX) The N types hold Unicode. Plain VARCHAR uses a code page unless the collation is UTF-8.
Oracle CHAR(n) VARCHAR2(n) CLOB VARCHAR2 is the recommended name. An empty string is stored as NULL.
SQL
-- PostgreSQL
CREATE TABLE tags (code CHAR(3), label VARCHAR(30), notes TEXT);
INSERT INTO tags (label) VALUES (repeat('x', 31));  -- ERROR: value too long for type character varying(30)

-- SQL Server
CREATE TABLE tags (label NVARCHAR(30), notes NVARCHAR(MAX));

Usage notes

  • Prefer VARCHAR or TEXT over CHAR. Fixed-width columns only make sense for values that really are always the same length, such as country codes ('DE') or currency codes.
  • Trailing spaces. Whether 'a' equals 'a ' depends on the database. SQLite and PostgreSQL treat them as different for VARCHAR; the SQL standard pads the shorter value in comparisons, and MySQL ignores trailing spaces in most comparisons.
  • Case sensitivity comes from the collation, not the type. SQLite is case-sensitive by default; use COLLATE NOCASE or LOWER() when you need case-insensitive matching.
  • Characters versus bytes. LENGTH() counts characters in most databases but bytes in a few (Oracle's LENGTHB, SQL Server's DATALENGTH). With multi-byte text, check which one you are getting. See LENGTH().
  • Enforce lengths yourself in SQLite with CHECK (length(code) <= 3) if you need the rule.
  • Index limits. MySQL limits indexed prefix lengths; very long TEXT columns are poor index candidates everywhere.
  • Do not store lists in a text column ('red,green,blue'). Use a related table instead.