What is the difference between [A-Z] and [[:upper:]] in MySQL REGEXP?

Q: What is the difference between [A-Z] and [[:upper:]] in MySQL REGEXP?

In MySQL REGEXP, `[A-Z]` and `[[:upper:]]` both match uppercase letters, but there are subtle differences in behavior depending on character sets and locale. **[A-Z]:** Matches only the ASCII uppercase letters from A to Z. It does not account for letters with accents or uppercase letters in non-ASCII character sets. **[[:upper:]]:** This is a POSIX character class that matches all uppercase letters according to the current locale, including accented letters or other uppercase letters outside the basic ASCII range. **Portability:** `[[:upper:]]` is generally more portable and locale-aware, making it safer for internationalized applications. **Usage in Patterns:** Both can be used with quantifiers and anchors, e.g., `^[A-Z]+$` or `^[[:upper:]]+$` to match strings consisting entirely of uppercase letters. In short, `[A-Z]` is limited to standard ASCII, while `[[:upper:]]` adapts to the locale and supports a wider range of uppercase characters. SELECT name FROM employees WHERE name REGEXP '^[A-Z]+$'; -- Matches 'ALICE', 'BOB', etc., ASCII only SELECT name FROM employees WHERE name REGEXP '^[[:upper:]]+$'; -- Matches 'ALICE', 'ÉMILIE', etc., locale-aware

Difficulty: 5/10

regex character classes, collation and Unicode, MySQL REGEXP

Difference Between [A-Z] and [[:upper:]] in MySQL REGEXP

In MySQL REGEXP, [A-Z] and [[:upper:]] both match uppercase letters, but there are subtle differences in behavior depending on character sets and locale.

Key Differences

1
[A-Z]: Matches only the ASCII uppercase letters from A to Z. It does not account for letters with accents or uppercase letters in non-ASCII character sets.
2
[[:upper:]]: This is a POSIX character class that matches all uppercase letters according to the current locale, including accented letters or other uppercase letters outside the basic ASCII range.
3
Portability: [[:upper:]] is generally more portable and locale-aware, making it safer for internationalized applications.
4

In short, [A-Z] is limited to standard ASCII, while [[:upper:]] adapts to the locale and supports a wider range of uppercase characters.

Example: Using [A-Z]

Example: Using [[:upper:]]

Scenario Questions

0-2 years experience

Follow-up Questions