Here are three options for returning rows that contain lowercase characters in MySQL.
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 lowercase letters.
Option 1: Compare to the UPPER() String
We can use the UPPER() function to compare the original value to its uppercase equivalent:
SELECT c1 FROM t1
WHERE CAST(UPPER(c1) AS BINARY) <> CAST(c1 AS BINARY);Result:
+----------------+ | c1 | +----------------+ | Café | | café | | 1café | | eCafé | | James Bond 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 uppercase equivalents. The reason we do this is because, if a value is the same as its uppercase equivalent, then it was already uppercase to begin with (and we don’t want to return it).
By default, MySQL performs a case-insensitive search, and so I use the CAST() function to cast the values to binary in order get a case-sensitive search.
It’s also possible to use the BINARY operator to cast the value to binary, but this operator is deprecated as of MySQL 8.0.27 and it could be removed in a future release.
In any case, this is what the code looks like when using the BINARY operator:
SELECT c1 FROM t1
WHERE BINARY UPPER(c1) <> BINARY c1;It can also be done like this:
SELECT c1 FROM t1
WHERE BINARY(UPPER(c1)) <> BINARY(c1);Here are the results I get from both of these when running the code in MySQL 8.0.27:
+----------------+ | c1 | +----------------+ | Café | | café | | 1café | | eCafé | | James Bond 007 | | é | | é 123 | | ø | +----------------+
And when I show the warnings:
SHOW WARNINGS;Result:
+---------+------+----------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------+ | Warning | 1287 | 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead | | Warning | 1287 | 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead | +---------+------+----------------------------------------------------------------------------------------------+
Option 2: Compare to the Actual Characters
Another option is to use the REGEXP operator with a regular expression pattern that explicitly includes each lowercase character we want to match:
SELECT c1 FROM t1
WHERE CAST(c1 AS BINARY) REGEXP CAST('[abcdefghijklmnopqrstuvwxyz]' AS BINARY);Result:
+----------------+ | c1 | +----------------+ | Café | | café | | 1café | | eCafé | | James Bond 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 those examples. Our result does contain é but that row was only returned because it also contains other lowercase 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 3: 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 CAST(c1 AS BINARY) REGEXP CAST('[a-z]' AS BINARY);Result:
+----------------+ | c1 | +----------------+ | Café | | café | | 1café | | eCafé | | James Bond 007 | +----------------+