LENGTH & CHAR_LENGTH
In this page:
Understanding Length
MySQL has two main ways to measure string sizes, and picking the wrong one can cause subtle bugs with non-ASCII text. One measures byte size, and the other measures character count, and they diverge as soon as multi-byte characters are involved.
Example: Understanding Length
SELECT LENGTH('cafe test') AS byte_length, CHAR_LENGTH('cafe test') AS char_length;Bytes vs Characters
Special characters and emojis take up more than one byte in UTF-8 encoding. LENGTH counts total bytes, while CHAR_LENGTH counts actual text characters, so a single emoji might report a LENGTH of 4 but a CHAR_LENGTH of 1.
Example: Bytes vs Characters
SELECT LENGTH('a') AS ascii_bytes, CHAR_LENGTH('a') AS ascii_chars;Empty and Null Strings
An empty string has 0 characters, distinct from having no value at all. If you check the length of a NULL value, MySQL returns NULL instead of a number, since you can't measure the length of something that isn't there.
Example: Empty and Null Strings
SELECT LENGTH('') AS empty_length, LENGTH(NULL) AS null_length;Trimming and Checking Length
Spaces count towards string length just like any other character. You can combine length functions with trimming functions like TRIM to count only real characters, ignoring accidental leading or trailing whitespace.
Example: Trimming and Checking Length
SELECT LENGTH(' hi ') AS padded_length, LENGTH(TRIM(' hi ')) AS trimmed_length;Performance and Filtering
You can use string lengths inside your WHERE clauses to filter out weak passwords or inputs that are too long, such as rejecting any password shorter than eight characters before it's even hashed.
Example: Performance and Filtering
CREATE TABLE users (id INT, password TEXT);
INSERT INTO users VALUES (1, 'abc'), (2, 'longenoughpw');
SELECT * FROM users WHERE LENGTH(password) < 8;Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: