SQLite Text Length: Characters, Bytes and NUL

SQLite’s length() counts different things for different values. For text, it counts Unicode code points before the first NUL character. For a BLOB, it counts bytes. Choose the value type and encoding before treating either result as a character limit or storage measurement.

For example, a text value containing A, U+0000 and B returns 1 from length(). In the UTF-8 examples below, converting that complete value to a BLOB gives three bytes. The shorter text result does not mean the trailing data disappeared.

Check what kind of value you are measuring

The SQLite function reference defines the text and BLOB rules. Use typeof(value) to inspect the actual value. A number passed to length() is measured through its text representation, and a NULL input produces NULL. An empty text value produces zero; NULL is a separate case.

SQLite’s storage classes and column affinity help explain why a column declaration alone is insufficient. Inspect the value returned by your query. Python’s SQLite binding maps str to TEXT, bytes to BLOB and None to NULL, so the object you bind can also change the measurement.

Compare six TEXT values in UTF-8

These executed examples use a UTF-8 SQLite connection and TEXT parameters. Quoted values use JSON notation: \u0000 labels one actual NUL, not six backslash-and-letter characters. The combining-accent row contains U+0065 U+0301. Its two code points remain distinct from the precomposed U+00E9 row.

"abc"

"é"

"😀"

"é"

"A\u0000B"

""

The NUL position is instr(value, char(0)): zero means none was found, while two identifies the second code point in the NUL example. The emoji has one code point and four UTF-8 bytes. Neither that code-point result nor the combining-accent result defines a user-perceived character count; the String Length guide covers the separate unit choice.

Run the NUL example without opening an existing database

This Python snippet creates an isolated in-memory connection, reads its encoding and binds the example text with a placeholder. It closes the connection explicitly. The sqlite3 documentation explains in-memory databases and parameter binding; pass text as a parameter rather than assembling it into SQL syntax.

import sqlite3

con = sqlite3.connect(":memory:")
try:
    encoding = con.execute("PRAGMA encoding").fetchone()[0]
    print("Encoding:", encoding)
    value = "A\u0000B"
    result = con.execute(
        "SELECT typeof(?1), length(?1), "
        "length(CAST(?1 AS BLOB)), instr(?1, char(0))",
        (value,)
    ).fetchone()
    print(result)
finally:
    con.close()

For the UTF-8 connection shown, the output is Encoding: UTF-8 followed by ('text', 1, 3, 2): value type, text length, converted BLOB bytes and NUL position. Binding b"A\x00B" as bytes instead gives a BLOB whose length() is three.

Read byte counts in their database encoding

CAST to BLOB uses the database connection’s encoding to obtain the text bytes. It is not an unconditional UTF-8 encoder. Read PRAGMA encoding on the connection you actually use. A database whose encoding is already established cannot be switched by assigning a different encoding afterward.

The combining-accent value in the table has three bytes in UTF-8. The same executed value has four bytes when converted to BLOB in a UTF-16le database, while its text length remains two. Where your SQLite build provides octet_length(), that function measures the text bytes directly and also depends on database encoding. Check availability in your actual runtime before relying on it.

Inspect NUL before treating the displayed text as complete

SQLite’s NUL guidance shows that text can retain an embedded NUL even when quote() or command-line output hides the suffix. In the example here, quote(value) produces 'A', but the full value is not equal to the one-letter string. Inspect the converted BLOB and NUL position when diagnosing that mismatch.

SQLite discourages NUL inside text strings. Treat its presence according to your data contract rather than silently removing it or the suffix to make a count pass. This guide supplies measurements and diagnostics; its queries do not clean an owner database.

These are SQLite value measurements, not the size of a database file or the original bytes of an imported upload. A receiving system’s field rule is another constraint, and another SQL engine may define length differently. For Python’s own text and occurrence functions, see Count in Python.