SQL char_length: Counting Characters, Not Bytes

When you validate a username or trim text to fit a limit, you almost always want to count characters, not bytes. In UTF-8 a single character can take one to four bytes, so a naive length check breaks the moment accents, Cyrillic, or CJK text appear. Below we look at how char_length differs from octet_length and why LENGTH is a poor choice for counting characters in portable code.

char_length counts characters

char_length (alias character_length) returns the number of characters in a string, regardless of how many bytes they occupy on disk. On plain Latin the result is obvious and matches what you see:

SELECT char_length('acai');        -- 4
SELECT char_length('cafe');        -- 4
SELECT char_length('Moscow');      -- 6

The first two words above stand for the accented "açaí" and "café": each is four characters, but in UTF-8 each accented letter takes two bytes, so the string is longer in bytes than in characters. The gap shows up the moment a value contains a non-ASCII character. Take a letter with an accent written as a plus a combining mark, U&'a\0301'. The eye sees one glyph, but UTF-8 encodes it as two bytes, so char_length and octet_length disagree:

-- one visible character, two bytes in UTF-8
SELECT char_length(U&'a\0301') AS chars,   -- 1
       octet_length(U&'a\0301') AS bytes;  -- 2

char_length answers "how many characters will a human see" and returns 1, while octet_length answers "how much space does this take in memory or on disk" and returns 2. The same logic carries over to substring and left: they operate on characters, so trimming by characters never slices a multibyte character in half, whereas hand-rolled byte slicing does and produces corrupted text.

octet_length and why bytes differ from characters

octet_length returns the size of a string in bytes. For pure ASCII it matches char_length, but for accented and ideographic text the two diverge. The query below puts both measures side by side so the gap is visible right in the output:

SELECT name,
       char_length(name)  AS chars,
       octet_length(name) AS bytes
FROM users
WHERE country IN ('BR', 'JP', 'RU');

A rough UTF-8 guide:

• Latin letters and digits: 1 byte per character;
• accents and Cyrillic: usually 2 bytes;
• most CJK ideographs: 3 bytes;
• emoji and rare signs: 4 bytes.

So a string of four ideographs gives char_length = 4 but octet_length = 12. That gap hits column limits directly: if a column is declared VARCHAR(10), Postgres enforces that limit in characters, so the value fits, while a hand-rolled byte limit of 10 bytes would reject it.

Length validation

Business rules almost always mean characters, so reach for char_length in filters and checks. Find users with a name that is too short and an email that is suspiciously long:

SELECT id, name, email
FROM users
WHERE char_length(name) < 2
   OR char_length(email) > 254;

The same rule as a CHECK constraint on char_length so a too-short or too-long name never reaches the table:

ALTER TABLE employees
  ADD CONSTRAINT name_len_chk
  CHECK (char_length(name) BETWEEN 2 AND 100);

octet_length earns its keep when you hit a hard technical ceiling in bytes, such as a key size in an external system or an index prefix:

SELECT id, email
FROM users
WHERE octet_length(email) > 191;   -- byte budget for an index prefix

In practice keep a simple rule in mind: limits meant for humans (name, title, comment) should count characters with char_length, while limits meant for machines (a key, an index, a fixed-width field in an external format) should count bytes with octet_length. Mixing the two measures in a single rule is a reliable way to ship a bug that only fires on accented and ideographic input, the kind you never see in Latin-only test data, where char_length and octet_length agree.

Gotcha: LENGTH behaves differently across engines

The biggest trap is LENGTH, because it exists everywhere but means something different in each engine.

• In PostgreSQL, length(text) counts characters (like char_length), but length(bytea) counts bytes.
• In MySQL, LENGTH() counts bytes, while characters come from CHAR_LENGTH().
• In ClickHouse, length() on a String counts bytes, and characters come from lengthUTF8().

In the examples below the string 'cafe' stands for "café", with an accent over the last letter, so in UTF-8 it takes 5 bytes for 4 characters. The byte function returns 5 and the character function returns 4; on the plain ASCII word cafe both would return 4 and the trap would stay invisible.

-- MySQL: byte count vs character count
SELECT LENGTH('cafe'),       -- 5 (e with accent = 2 bytes)
       CHAR_LENGTH('cafe');  -- 4

-- ClickHouse: byte count vs character count
SELECT length('cafe'),       -- 5
       lengthUTF8('cafe');   -- 4

Because of this divergence, a length check written in Postgres with length() silently turns from a character check into a byte check when ported to MySQL or ClickHouse: on the accented word café, length() gives 4 in Postgres but 5 in MySQL and ClickHouse. On Latin data the tests pass, but the first row with an accent or Cyrillic steps over a limit that used to hold. So when you migrate, run length checks against strings with NULL, empty values, accents, Cyrillic, and emoji, not only ASCII data where length, char_length, and octet_length agree.

The same risk touches performance: char_length(col) in a WHERE or CHECK is a function over the column, and it can hide a plain index on that column from the planner. If the length rule runs on a large table, look at the execution plan and, if needed, add an expression index or store the length in a generated column instead of recomputing it on every query.

The takeaway is simple: never rely on LENGTH for character counts in portable code. Spell out char_length in Postgres, CHAR_LENGTH in MySQL, and lengthUTF8 in ClickHouse, so your validation rules stay stable when you migrate data full of accents and ideographs. And note one more subtlety: char_length counts Unicode code points, not the "graphemes" a human perceives. An emoji with a skin-tone modifier or a two-symbol flag can report a length greater than one. For most validation that is fine, but if you trim by displayed positions, counting "visible" characters needs separate logic in the application layer.