SQL LENGTH: Separate Text Characters From Storage Bytes

The practice task

Compare fictional labels containing ASCII, an emoji and a combining accent. The result shows why a visible-character limit cannot be inferred from a byte count alone.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE text_samples(id INTEGER, value TEXT);
INSERT INTO text_samples VALUES (1,'Ari'),(2,'A🚀'),(3,'e' || CHAR(769)),(4,''),(5,NULL);

SELECT id, LENGTH(value) AS code_points,
 LENGTH(CAST(value AS BLOB)) AS utf8_bytes
FROM text_samples ORDER BY id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

Ari has three ASCII characters and three bytes. A plus the rocket has two code points but five UTF-8 bytes. The e-plus-combining-accent label has two code points even though a reader may perceive one accented character.

The empty string has a known length of zero. NULL has no known text value, so its length remains NULL. That difference can matter when checking whether a required field was supplied rather than simply measuring a present value.

The byte column describes the UTF-8 representation in this SQLite exercise. Another database function or encoding may measure a different unit. Specify whether a validation rule concerns storage, code points or user-perceived characters before choosing a length function.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Name the measured unit in the result.
• Test an emoji and a combining sequence when Unicode matters.
• Keep empty text and missing text distinct.