In SQL, why do CHAR_LENGTH and OCTET_LENGTH give different results for a customer name like 'José', and when do you use each?
answer
- what each function is counting
- the encoding decides one of them
- LENGTH is the ambiguous word
- what the limit actually measures
- a visible letter may be two
basics
~20 sCHAR_LENGTH counts characters and OCTET_LENGTH counts bytes. In UTF-8 the é in 'José' takes two bytes, so the results are 4 and 5. Use characters for rules people reason about, and bytes for storage or payload limits.
solid answer
~50 s`CHAR_LENGTH` counts characters and `OCTET_LENGTH` counts the bytes of the encoded value, so they agree only while every character takes one byte. In UTF-8 an ASCII letter takes one byte, `é` takes two and an emoji takes four, which makes `'José'` 4 and 5. I use character counts for rules a person thinks about, such as a name limit. I use bytes when the limit really is bytes, such as a payload size or a column defined in bytes. The trap is the word `LENGTH`: in MySQL it means bytes, in PostgreSQL and Oracle it means characters, and SQL Server spells the pair `LEN` and `DATALENGTH`, with `LEN` also ignoring trailing spaces. So a length check can change meaning when a query moves between engines. And a character here is usually a code point, so a decomposed `é`, written as `e` plus a combining accent, counts as two.
go deeper
Recall that one function counts characters and the other counts bytes, and work out 'José' in UTF-8 as 4 and 5. Know that an accented letter takes more than one byte.
Explain why encoding changes the byte count but not the character count, and warn that LENGTH means bytes on MySQL but characters on PostgreSQL and Oracle. Match the function to what the limit measures.
Diagnose length-related import rejections: a limit in bytes applied to multi-byte names, a query moved across engines, or decomposed accents inflating a count. Show how you would audit the column before picking a limit.
Set the team's rule for text limits: stated in characters unless a system forces bytes, with one normalised form on the way in. It avoids each service validating the same name differently.
## Characters and bytes A database stores text as bytes, and an **encoding** is the rule that maps characters to those bytes. **UTF-8**, the common choice today, uses between one and four bytes per character. **`CHAR_LENGTH`** (also spelled `CHARACTER_LENGTH`) answers how many characters a value holds. **`OCTET_LENGTH`** answers how many bytes it occupies; an octet is simply an eight-bit byte. The two numbers match only for text whose every character takes one byte, such as plain ASCII in UTF-8. That is why the difference stays hidden until real customer names arrive in an import. ## Worked numbers These are the UTF-8 results, checked on PostgreSQL: | Value | `CHAR_LENGTH` | `OCTET_LENGTH` | Why | |---|---|---|---| | `'Jose'` | 4 | 4 | Every character is one byte | | `'José'` | 4 | 5 | `é` as one character takes two bytes | | `'cafe'` plus a combining accent | 5 | 6 | The accent is its own character, of two bytes | | A single emoji | 1 | 4 | One character outside the 16-bit range | The byte count depends on the encoding, so the same text in a single-byte encoding such as Latin-1 would give 4 bytes for `'José'`. `OCTET_LENGTH` is therefore a statement about how the value is stored, while `CHAR_LENGTH` is a statement about the text. ## LENGTH across engines The trouble in practice comes less from the two standard-looking names and more from the function everyone actually types: | Engine | Characters | Bytes | |---|---|---| | PostgreSQL | `CHAR_LENGTH`, `LENGTH` | `OCTET_LENGTH` | | MySQL | `CHAR_LENGTH` | `LENGTH`, `OCTET_LENGTH` | | Oracle | `LENGTH` | `LENGTHB` | | SQL Server | `LEN`, which ignores trailing spaces | `DATALENGTH` | So `LENGTH(name) <= 20` is a limit in characters on PostgreSQL and Oracle and a limit in bytes on MySQL. A query moved between them keeps running and silently starts rejecting different rows, which makes this portability bug hard to spot, because no error points at it. ## Which one to use - **Business rules** about what a person typed, such as a maximum name length, belong in characters. - **Storage and transport limits**, such as a payload size, an external system's field limit or a column whose size is declared in bytes, belong in bytes. - **A suspected truncation** is a character question: compare `CHAR_LENGTH` of the source and target values, because byte counts differ legitimately when the two sides use different encodings. - **Display width** is neither. A wide character or a combining accent means neither count tells you how much screen a value takes. - **Slicing** should match too. On PostgreSQL and MySQL, `SUBSTRING` counts characters, so pair it with a character count; cutting by byte offset in client code can split a multi-byte character in half. ## Code points are not what people see A **character** in these functions is normally a Unicode **code point**, and a letter a reader sees can be more than one: - `é` can be stored as one code point or as `e` followed by a combining accent. Both look identical on screen, and the second counts as 2 in `CHAR_LENGTH`. - Those two forms also fail an equality test against each other on many collations, which is a separate cleaning problem. PostgreSQL offers `normalize` to convert a value to one form. - SQL Server, with `nvarchar` and a collation without supplementary-character support, counts an emoji as 2, because it counts UTF-16 units rather than code points. ## Using both to audit an import The gap between the two functions is itself a useful tool on a UTF-8 column: 1. Find rows holding any non-ASCII character with `WHERE CHAR_LENGTH(name) <> OCTET_LENGTH(name)`. 2. Measure how much real text is multi-byte before you set a limit in bytes, by comparing `MAX(CHAR_LENGTH(name))` with `MAX(OCTET_LENGTH(name))`. 3. Normalise first, if your engine offers it, and only then apply a length rule, so that two spellings of one letter do not get different treatment. One more edge on fixed-width columns: PostgreSQL's `CHAR_LENGTH` on a `CHAR(5)` value holding `'abc'` returns 3, since trailing padding is not counted, while `OCTET_LENGTH` on the same value returns 5, because that version of the function keeps the padding. Comparing the two on `CHAR(n)` therefore measures padding as well as encoding, so run that audit on variable-length columns.
- How can the two functions together find rows with non-ASCII text in a UTF-8 column?Filter on CHAR_LENGTH(name) <> OCTET_LENGTH(name). In UTF-8 only ASCII characters take a single byte, so any character outside ASCII makes the byte count larger. It is a quick audit for an import, needs no pattern matching, and does not work the same way in a single-byte encoding, where the two counts always match.
- A limit of 20 is checked with CHAR_LENGTH, yet a name that looks shorter is rejected; how can that happen?CHAR_LENGTH usually counts code points, not letters as a reader sees them. A name written with combining accents holds extra code points, so it can pass 20 while looking like 18. Normalising to composed form first, with normalize on PostgreSQL, removes most of the gap. Characters with no composed form still count as several.
- Does the 10 in VARCHAR(10) limit characters or bytes?It depends on the engine. PostgreSQL and MySQL count characters. SQL Server's varchar(10) counts bytes, and Oracle's VARCHAR2(10) counts bytes by default unless declared with CHAR or the session default says otherwise. So one accented ten-letter name can fit on one engine and fail on another, which matters when a schema moves.
- CHAR_LENGTH and OCTET_LENGTH always match, because one character is stored in one byte.
- LENGTH returns characters on every engine, so it is safe to use in portable code.
- The OCTET_LENGTH of a string stays the same whatever encoding the value is stored in.
- CHAR_LENGTH counts what a reader sees, so a combining accent adds nothing to the result.
- An emoji counts as exactly one character in the length function of every engine.