SQLite TEXT, explained

TEXT holds strings — names, emails, JSON, whole documents. It is also where SQLite surprises people most: the (255) in VARCHAR(255) does nothing at all. Here’s how text is stored, compared and limited.

In one paragraph (ELI12)

TEXT is letters and words: a name, an email, a sentence, a whole book. SQLite writes down the characters (as UTF-8 bytes) and remembers how many bytes there were. It doesn’t care how long the text is — one letter or millions — and it never chops anything off.

How it’s stored

A TEXT value’s serial type is 2·n + 13 for n bytes (odd numbers ≥ 13), and the bytes follow in the database’s text encoding — UTF-8 almost always, set once for the whole file in header bytes 56–59 (the viewer’s METADATA tab shows it). There is no terminator and no padding: 'hi' costs a one-byte serial type plus two bytes.

// serial type → size, as in sqlite-format.js
function serialSize(t) {
  if (t >= 12) return (t - (t & 1 ? 13 : 12)) / 2;   // odd = TEXT, even = BLOB
  return [0, 1, 2, 3, 4, 6, 8, 8, 0, 0, 0, 0][t];
}

Long values don’t fit in one page. The first part stays in the b-tree cell and the rest goes to a chain of overflow pages, each holding page-size minus 4 bytes plus a pointer to the next. That’s why reading a huge TEXT column is slower than reading a short one: every overflow page is another read.

VARCHAR(n) isn’t enforced

SQLite accepts VARCHAR(255), CHAR(2), NVARCHAR(40) and friends for compatibility, uses them only to pick TEXT affinity, and ignores the number. A CHAR(2) column will store 'Deutschland' without complaint, and CHAR(10) doesn’t pad. If you need a limit, say so:

CREATE TABLE countries (
  code TEXT NOT NULL CHECK (length(code) = 2),
  name TEXT NOT NULL CHECK (length(name) <= 120)
);

Comparing and sorting

Text compares with a collation: BINARY (the default — byte by byte, so 'B' < 'a'), NOCASE (folds ASCII A–Z only) or RTRIM (ignores trailing spaces). Set it per column or per expression:

CREATE TABLE users (email TEXT COLLATE NOCASE UNIQUE);
SELECT name FROM artists ORDER BY name COLLATE NOCASE;

Across storage classes the order is fixed: NULL < numbers < TEXT < BLOB. So a TEXT value '10' sorts after every number, including 999.

Examples

CREATE TABLE t (code CHAR(2), note TEXT);
INSERT INTO t VALUES ('Deutschland', 42);
SELECT code, length(code), note, typeof(note) FROM t;
-- Deutschland | 11 | 42 | text      ← the 42 became the text '42'

SELECT length('café'), length(CAST('café' AS BLOB));
-- 4 | 5                              ← characters vs UTF-8 bytes

SELECT json_extract('{"a":{"b":[1,2]}}', '$.a.b[1]');
-- 2                                  ← JSON is just TEXT with functions

Limits

• Longest string: 1,000,000,000 bytes by default (SQLITE_MAX_LENGTH; a build can raise it to 2,147,483,647).
• length() counts characters; octet_length() (3.43+) or length(CAST(x AS BLOB)) counts bytes.
• upper(), lower() and NOCASE only understand ASCII unless SQLite was built with ICU.
• The viewer shows the first 400 characters of a long value and its full byte length; use substr() in QUERY to read further.

Gotchas

• Numbers stored as text sort as text: '10' < '9'. A TEXT-affinity column converts numbers you insert into text.
• LIKE is case-insensitive for ASCII; GLOB is case-sensitive and uses */?.
• Trailing spaces count: 'a' = 'a ' is false under BINARY.
• An empty string is not NULL. '' is TEXT of length 0.

Storage class vs. declared type vs. affinity

SQLite is dynamically typed: the type belongs to the value, not the column. Every value is one of five storage classes — NULL, INTEGER, REAL, TEXT, BLOB. A column’s declared type only sets its affinity: a preference SQLite applies when you store a value, by five rules checked in order:

Order matters: CHARINT matches rule 1 before rule 2 and is INTEGER; FLOATING POINT contains INT and is INTEGER too. The viewer implements exactly these rules:

function affinityOf(declType) {
  const t = String(declType || '').toUpperCase();
  if (t.includes('INT')) return 'INTEGER';
  if (t.includes('CHAR') || t.includes('CLOB') || t.includes('TEXT')) return 'TEXT';
  if (t.includes('BLOB') || t.trim() === '') return 'BLOB';
  if (t.includes('REAL') || t.includes('FLOA') || t.includes('DOUB')) return 'REAL';
  return 'NUMERIC';
}

The other types

Open any .sqlite, .db or .sqlite3 file in the SQLite viewer — every column header has an ELI12 button that explains its type and links here. How the viewer reads a 20 GB database without loading it: how to open a 20 GB SQLite database in the browser.