Here are four options for returning rows that contain uppercase characters in MariaDB.

Sample Data

Suppose we have a table with the following data:

SELECT c1 FROM t1;

Result:

+----------------+
| c1             |
+----------------+
| CAFÉ           |
| Café           |
| café           |
| 1café          |
| eCafé          |
| James Bond 007 |
| JB 007         |
| 007            |
| NULL           |
|                |
| É              |
| É 123          |
| é              |
| é 123          |
| ø              |
| Ø              |
+----------------+

We can use the following methods to return the rows that contain uppercase letters.

Option 1: Compare to a POSIX Character Class

We can use the REGEXP operator to perform a match of a regular expression pattern.

A simple option is to compare our column to the [:upper:] POSIX character class:

SELECT c1 FROM t1
WHERE c1 REGEXP '[[:upper:]]';

Result:

+----------------+
| c1             |
+----------------+
| CAFÉ           |
| Café           |
| eCafé          |
| James Bond 007 |
| JB 007         |
| É              |
| É 123          |
| Ø              |
+----------------+

RLIKE is a synonym for REGEXP, so we can replace REGEXP with RLIKE to get the same result:

SELECT c1 FROM t1
WHERE c1 RLIKE '[[:upper:]]';

Option 2: Compare to the LOWER() String

Another option is to use the LOWER() function to compare the original value to its lowercase equivalent:

SELECT c1 FROM t1
WHERE BINARY LOWER(c1) <> BINARY c1;

Result:

+----------------+
| c1             |
+----------------+
| CAFÉ           |
| Café           |
| eCafé          |
| James Bond 007 |
| JB 007         |
| É              |
| É 123          |
| Ø              |
+----------------+

By using the not equal to (<>) operator (you can alternatively use != instead of <> if you prefer), we only return those rows that are different to their lowercase equivalents. The reason we do this is because, if a value is the same as its lowercase equivalent, then it was already lowercase to begin with (and we don’t want to return it).

By default, MariaDB performs a case-insensitive search, and so I use the BINARY operator to get a case-sensitive search.

This can also be done like this:

SELECT c1 FROM t1
WHERE BINARY(LOWER(c1)) <> BINARY(c1);

Option 3: Compare to the Actual Characters

Another option is to use the REGEXP operator with a regular expression pattern that explicitly includes each uppercase character we want to match:

SELECT c1 FROM t1
WHERE BINARY c1 REGEXP BINARY '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]';

Result:

+----------------+
| c1             |
+----------------+
| CAFÉ           |
| Café           |
| eCafé          |
| James Bond 007 |
| JB 007         |
+----------------+

This time less rows are returned than in the previous examples. That’s because I didn’t specify characters like É and Ø, which were returned in the previous example. Our result does contain É but that row was only returned because it also contains other uppercase characters that do match.

Therefore, you’ll need to make sure that you’ve got all valid characters covered if you use this option.

Option 4: Compare to a Range of Characters

Another way to do it is to specify the range of characters we want to match:

SELECT c1 FROM t1
WHERE BINARY c1 REGEXP BINARY '[A-Z]';

Result:

+----------------+
| c1             |
+----------------+
| CAFÉ           |
| Café           |
| eCafé          |
| James Bond 007 |
| JB 007         |
+----------------+