CHAR_LENGTH()
SQL · String Functions
Theory
CHAR_LENGTH(string), also written CHARACTER_LENGTH, is the SQL-standard function that returns the number of characters in a string. It always counts characters, never bytes. The difference matters for text outside basic English. In UTF-8, an English letter takes 1 byte but a Hindi letter takes 3, so 'जयपुर' is 5 characters but 15 bytes.
In MySQL this is where people get caught: LENGTH('जयपुर') returns 15 (bytes) while CHAR_LENGTH returns 5. Postgres and MySQL support CHAR_LENGTH; Postgres LENGTH is already character-based. SQLite has no CHAR_LENGTH, but its LENGTH counts characters for text and bytes for a BLOB, which the diagram uses to show both. The standard function for byte count is OCTET_LENGTH. Note that a character here is a Unicode code point, so a Hindi letter with a vowel sign (like पु) counts as 2.
Why we use it
Column size limits, form validation and SUBSTRING positions are about characters, not bytes. CHAR_LENGTH gives the true character count regardless of language, which avoids bugs when your data includes Hindi names or emojis.
When to use it
Use CHAR_LENGTH (or a character-based LENGTH) for validation and display width. Use OCTET_LENGTH or a byte function only when you care about storage size. In SQL Server use LEN; in SQLite use LENGTH on text.
How it works
- Take the input string
- Is it NULL? Yes: Return NULL. No: Decode it as characters.
- Count characters (code points), not bytes
- Multi-byte letters such as Hindi still count as 1 each
- Return the character count
Syntax
CHAR_LENGTH(string)Example
SELECT CHAR_LENGTH(name) FROM employees;Key points
- Always character-based, never byte-based
- SQL-standard name; CHARACTER_LENGTH is a synonym
- Safer than LENGTH() in MySQL for Hindi or emoji text
- OCTET_LENGTH() is the standard byte-count function
- SQLite has only LENGTH(), which counts characters for text