SQL String and Character Data Types: VARCHAR, CHAR, and TEXT

A customer table has a first_name column, an email column, and a notes field. Each of these holds text, but they are not the same kind of text. Choosing the wrong type for each one leads to wasted storage, broken comparisons, or data that silently gets truncated. String types are the most common data type you will encounter in any real schema, so understanding how they differ saves you from avoidable headaches.

CHAR: Fixed-Length Strings

CHAR(n) stores exactly n characters. If you insert a shorter value, the database pads it with trailing spaces to fill the defined length.

This makes CHAR a good fit for columns where every value is always the same length, such as country codes, gender codes, or fixed-format status flags.

-- country is a good candidate for CHAR(2) if using ISO codes
-- Here we query it as a regular TEXT column in SQLite
SELECT c.customer_id, c.first_name, c.country
FROM customers c
WHERE c.country = 'US'
ORDER BY c.last_name;

In practice you will mostly encounter CHAR in older schemas or in columns holding codes and abbreviations. In SQLite, CHAR(n) is stored as TEXT and the padding behaviour does not apply, but knowing how it works in MySQL and PostgreSQL matters when you move between environments.

VARCHAR: Variable-Length Strings

VARCHAR(n) stores up to n characters. It only uses as much space as the actual value requires. It is the most common string type you will see in real schemas.

Columns like first_name, last_name, email, city, sku, and brand are all natural candidates for VARCHAR.

-- Filter customers by email domain using string functions on a VARCHAR-style column
SELECT c.customer_id, c.first_name, c.last_name, c.email
FROM customers c
WHERE c.email LIKE '%@gmail.com'
ORDER BY c.last_name;

-- Check product SKUs and brand names, both typical VARCHAR columns
SELECT p.sku, p.brand, p.product_name, p.price
FROM products p
WHERE p.brand = 'Nike'
  AND p.is_active = 1;

TEXT: Unlimited-Length Strings

TEXT stores strings of arbitrary length. Use it when you cannot or do not want to cap the length, such as order notes, product description, or failure_reason in payments.

-- Pull orders that have notes attached
SELECT o.order_id, o.status, o.notes
FROM orders o
WHERE o.notes IS NOT NULL
  AND o.notes != ''
ORDER BY o.order_date DESC
LIMIT 20;

-- Find payments with a recorded failure reason
SELECT pay.payment_id, pay.order_id, pay.method, pay.failure_reason
FROM payments pay
WHERE pay.failure_reason IS NOT NULL
ORDER BY pay.payment_date DESC;

TEXT columns are not ideal for columns you filter or join on frequently, because indexes on very long text columns can be large and slow. For short, bounded values, stick with VARCHAR.

How SQLite Handles String Types

SQLite uses a single internal storage class called TEXT for all string data. When you declare a column as VARCHAR(100) or CHAR(5), SQLite assigns it TEXT affinity and stores the value as-is without enforcing length or padding.

| Declared type | SQLite affinity | Length enforced? | |---------------|----------------|-----------------| | CHAR(n) | TEXT | No | | VARCHAR(n) | TEXT | No | | TEXT | TEXT | No | | CLOB | TEXT | No |

This means a VARCHAR(10) column in SQLite happily stores a 200-character string. In MySQL or PostgreSQL it would not. Keep this in mind when you write schemas intended to run on multiple databases.

-- SQLite stores all of these as TEXT regardless of declared type
-- Demonstrate by selecting string columns of different declared types
SELECT
    c.email,
    c.zip_code,
    c.loyalty_tier,
    c.gender
FROM customers c
LIMIT 5;

email might be declared VARCHAR(255), zip_code as CHAR(10), loyalty_tier as VARCHAR(20), and gender as CHAR(1). In SQLite, all four behave identically at the storage layer.

String Comparisons and Case Sensitivity

By default, SQLite string comparisons are case-sensitive for LIKE when you use the GLOB operator, but LIKE itself is case-insensitive for ASCII characters. This trips people up when filtering text columns.

-- LIKE is case-insensitive for ASCII in SQLite
SELECT c.first_name, c.email
FROM customers c
WHERE c.first_name LIKE 'john%';
-- Returns 'John', 'JOHN', 'john'

-- Use LOWER() to normalise before comparing when you want guaranteed case-insensitivity
SELECT c.first_name, c.email
FROM customers c
WHERE LOWER(c.email) LIKE '%@outlook.com';

Common Mistakes

-- wrong
SELECT c.first_name, c.last_name
FROM customers c
WHERE c.loyalty_tier = 'gold';

-- correct
SELECT c.first_name, c.last_name
FROM customers c
WHERE LOWER(c.loyalty_tier) = 'gold';

String comparisons are case-sensitive in many contexts. If stored values are mixed case, normalise with LOWER() or UPPER() before comparing.

---

-- wrong
SELECT c.customer_id, c.zip_code
FROM customers c
WHERE c.zip_code = 10001;

-- correct
SELECT c.customer_id, c.zip_code
FROM customers c
WHERE c.zip_code = '10001';

zip_code is a text column, not a number. Comparing it to an integer literal can produce no results or unexpected behaviour. Always quote string values in comparisons.

---

-- wrong
SELECT c.first_name, c.last_name, c.email
FROM customers c
WHERE c.email = '';

-- correct
SELECT c.first_name, c.last_name, c.email
FROM customers c
WHERE c.email IS NULL OR c.email = '';

An empty string and NULL are not the same thing. A missing email may be stored as NULL, as an empty string, or both depending on how data was loaded. Check for both when looking for missing values.

Quick Recap

• CHAR(n) is fixed-length and pads with spaces. Best for codes and flags where length never varies.
• VARCHAR(n) is variable-length and stores only what you insert. The right default for most string columns like names, emails, and codes.
• TEXT has no length limit and suits long or unpredictable content like notes and descriptions. SQLite stores all three types identically as TEXT, but length limits still matter if your schema needs to run on MySQL or PostgreSQL.

SELECT c.first_name, c.last_name, c.email FROM customers c WHERE c.email LIKE '%@gmail.com' ORDER BY c.last_name ASC;

SELECT c.customer_id, c.first_name, c.last_name, c.loyalty_tier FROM customers c WHERE LOWER(c.loyalty_tier) = 'gold' AND c.country = 'US' ORDER BY c.last_name;

SELECT e.first_name, e.last_name, e.email, COUNT(o.order_id) AS order_count FROM employees e JOIN orders o ON e.employee_id = o.employee_id WHERE LOWER(e.department) = 'sales' GROUP BY e.employee_id, e.first_name, e.last_name, e.email HAVING COUNT(o.order_id) >= 1 ORDER BY order_count DESC;