STRING types (VARCHAR)

Concepts covered: Basic Types, String Length

VARCHAR stores text: a name, an email address, a product title, a log line. Text differs from an integer in one structural way, which is that its values vary in length. Every INTEGER occupies 4 bytes; 'Hi' is 2 characters and 'Hello World' is 11, and a VARCHAR value stores the characters actually present plus a small header recording how many there are. The n in VARCHAR(n) is a ceiling, the longest value the column accepts, and a longer value is rejected or truncated depending on the database.

Single quotes for text, double quotes for names

A string literal is written in single quotes: 'Alice'. Standard SQL reserves double quotes for something else, an identifier such as a table or column name, so in PostgreSQL "Alice" asks for a column called Alice and fails with column "Alice" does not exist. SQLite, and MySQL in its default mode, quietly accept "Alice" as the text Alice instead, so the mistake works until the query moves to a stricter engine, which is why the single-quote habit matters. An apostrophe inside a literal is written as 2 single quotes, 'O''Brien', which the database reads as 1 character.

SELECT
  'Alice' AS plain_text,
  'O''Brien' AS with_apostrophe,
  '42' AS looks_numeric
FROM customers

VARCHAR, CHAR and TEXT

Three character types cover almost every text column, and the choice turns on whether the values share a fixed length.

VARCHAR(n)

• Variable length, up to n characters
• Stores the characters present and a length header
• 'US' in VARCHAR(10) occupies 2 characters
• Names, emails, titles, anything whose length varies

CHAR(n)

• Fixed length, always exactly n characters
• Pads shorter values with trailing spaces
• 'US' in CHAR(10) is stored as 'US '
• Codes with one known length: country, currency, state

TEXT is a VARCHAR with no declared ceiling, and the practice database stores every string column that way. PostgreSQL treats VARCHAR without a length and TEXT identically; MySQL and SQL Server cap VARCHAR and reserve TEXT for long documents. A declared length reads best as a validation rule: VARCHAR(2) on a country code column rejects 'USA' at insert time, before the bad value reaches a report.

What happens to an over-length value depends on the engine. PostgreSQL rejects 'USA' in a VARCHAR(2) column with value too long for type character varying(2). MySQL in strict mode, its default since version 5.7, raises an error too, and with strict mode off it stores the first 2 characters, 'US', and records only a warning. SQLite ignores the declared length entirely and stores all 3 characters, so a length limit validates data only on engines that enforce it.

Measuring text with LENGTH

LENGTH returns how many characters a value holds, and it is the first string function most queries need: finding titles that overflow a UI, checking that a code has exactly 3 characters, spotting a blank value that is 0 characters long. The tickets table holds 5 support ticket titles of different lengths, one of them empty.

SELECT ticket_id, title, LENGTH(title) AS chars
FROM tickets
ORDER BY chars DESC

Ticket 3 is the row worth noticing: an empty string is a real value with length 0, and it is a different thing from a missing value. A filter WHERE LENGTH(title) < 5 is the standard way to find titles that carry no information. LENGTH counts characters, and the number of bytes behind those characters can be larger.

Spaces count as characters too. A title loaded as 'Dark mode ' with a trailing space has a LENGTH of 10, one more than 'Dark mode', and in PostgreSQL and SQLite the 2 values compare as unequal, so 2 tickets that look identical in a report land in separate groups. LENGTH exposes the difference where the eye cannot: 2 values that print the same and measure differently differ in whitespace.

The quoting rule is the one that catches new readers most often, because 2 of the 3 wrong forms look reasonable until the database answers.

Which form does standard SQL, and PostgreSQL, read as the text value Alice in a SELECT list?

• "Alice"
• 'Alice'
• Alice
• 'Alice's'

About this lesson

This section is part of the Data Types: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.