Introduction

Filtering rows by string length in MySQL is simple in syntax but easy to get subtly wrong. The main choices are whether you mean characters or bytes, whether surrounding whitespace should count, and whether null values should be considered valid input or excluded explicitly.

Use CHAR_LENGTH for Character-Based Rules

If the rule is "return rows where the field contains more than one character", CHAR_LENGTH is usually the correct function.

SELECT id, username
FROM users
WHERE CHAR_LENGTH(username) > 1;

This works well for ordinary validation rules such as "username must have at least two visible characters". It is usually a better choice than LENGTH because it counts characters rather than bytes.

Know the Difference Between CHAR_LENGTH and LENGTH

MySQL exposes two similar functions:

• 'CHAR_LENGTH(col) counts characters'
• 'LENGTH(col) counts bytes'

That difference matters as soon as multibyte encodings enter the picture.

SELECT
    CHAR_LENGTH('é') AS char_count,
    LENGTH('é') AS byte_count;

On a UTF-8 system, those values can differ. If the business rule is about what a user sees, CHAR_LENGTH is almost always the right answer.

Trim Input if Whitespace Should Not Count

If values may contain leading or trailing spaces, trim them before checking the length.

1SELECT id, username
2FROM users
3WHERE username IS NOT NULL
4  AND CHAR_LENGTH(TRIM(username)) > 1;

Without TRIM, a value such as ' a ' passes because the raw length is greater than one even though the meaningful content is just a single character.

This is especially important in cleanup queries and imports from older systems where whitespace rules were inconsistent.

Handle Null Values Deliberately

CHAR_LENGTH(NULL) returns NULL, not zero. In a WHERE clause that means the row does not match, but writing the null policy explicitly is usually clearer.

1SELECT id, username
2FROM users
3WHERE username IS NOT NULL
4  AND CHAR_LENGTH(TRIM(username)) > 1;

That query documents intent more clearly than relying on SQL's three-valued logic implicitly.

Use the Same Rule for Cleanup Queries

The inverse query is often just as useful. If you want to find rows that violate the rule, flip the predicate.

1SELECT id, username
2FROM users
3WHERE username IS NULL
4   OR CHAR_LENGTH(TRIM(COALESCE(username, ''))) <= 1;

This is useful when auditing imported data or preparing to add a stricter application or database constraint.

Consider Performance on Large Tables

Function-based filters such as CHAR_LENGTH(TRIM(username)) > 1 can make it harder for a plain index on username to help. On small tables that is often irrelevant. On large tables or heavily repeated reports, a generated column can make the rule easier to index.

1ALTER TABLE users
2ADD COLUMN username_len INT
3    GENERATED ALWAYS AS (CHAR_LENGTH(TRIM(username))) STORED,
4ADD INDEX idx_username_len (username_len);

Then the filter becomes:

SELECT id, username
FROM users
WHERE username_len > 1;

That pushes the normalization cost to write time and can make repeated filtering simpler.

Keep the Rule Consistent Across Layers

If the application validates usernames one way and the SQL query measures them another way, the system becomes inconsistent. Decide once whether spaces count, whether multibyte characters count by bytes or characters, and whether nulls should be allowed.

Most bugs around length checks are not SQL syntax bugs. They are mismatches between business rules and implementation details across different layers of the system.

Common Pitfalls

• Using LENGTH when the rule is about characters rather than bytes.
• Forgetting to trim whitespace before applying the length rule.
• Relying on implicit null behavior instead of stating the null policy clearly.
• Assuming function-based filters will use ordinary indexes efficiently on large tables.
• Enforcing one length rule in application code and a different one in SQL.

Summary

• Use CHAR_LENGTH for character-based length checks in MySQL.
• Add TRIM when surrounding whitespace should not count.
• Handle nulls explicitly so the query documents its policy.
• Consider a generated column if this filter is frequent on a large table.
• Keep the definition of "length greater than 1" consistent across the whole stack.