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.
CREATE TABLE tags ( code CHAR(3), label VARCHAR(30), notes TEXT );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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. |
-- 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
VARCHARorTEXToverCHAR. 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 forVARCHAR; 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 NOCASEorLOWER()when you need case-insensitive matching. - Characters versus bytes.
LENGTH()counts characters in most databases but bytes in a few (Oracle'sLENGTHB, SQL Server'sDATALENGTH). With multi-byte text, check which one you are getting. SeeLENGTH(). - Enforce lengths yourself in SQLite with
CHECK (length(code) <= 3)if you need the rule. - Index limits. MySQL limits indexed prefix lengths; very long
TEXTcolumns are poor index candidates everywhere. - Do not store lists in a text column (
'red,green,blue'). Use a related table instead.