Here are four options for returning rows that contain lowercase characters in Oracle Database.
Sample Data
Suppose we have a table with the following data:
SELECT c1 FROM t1;Result:
CAFÉ Café café 1café eCafé James Bond 007 JB 007 007 É É 123 é é 123 ø Ø
We can use the following methods to return the rows that contain uppercase letters.
Option 1: Compare to a POSIX Character Class
Oracle’s REGEXP_LIKE condition complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. We can therefore use the [:lower:] POSIX character class to check for lowercase characters:
SELECT c1 FROM t1
WHERE REGEXP_LIKE(c1, '[[:lower:]]');Result:
Café café 1café eCafé James Bond 007 é é 123 ø
Option 2: 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 UPPER(c1) <> c1;Result:
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, Oracle performs a case-sensitive search, and so I don’t need to do anything else to the query to make it case-sensitive.
Option 3: Compare to the Actual Characters
Another option is to use the REGEXP_LIKE condition with a regular expression pattern that explicitly includes each lowercase character we want to match:
SELECT c1 FROM t1
WHERE REGEXP_LIKE(c1, '[abcdefghijklmnopqrstuvwxyz]', 'c');Result:
Café café 1café eCafé James Bond 007
The 'c' specifies case-sensitive and accent-sensitive matching, even if the determined collation of the condition is case-insensitive or accent-insensitive.
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.
Here it is again with those two characters included:
SELECT c1 FROM t1
WHERE REGEXP_LIKE(c1, '[éøabcdefghijklmnopqrstuvwxyz]', 'c');Result:
Café café 1café eCafé James Bond 007 é é 123 ø
Option 4: Compare to a Range of Characters
Another way to do it is to specify the range of uppercase characters we want to match:
SELECT c1 FROM t1
WHERE REGEXP_LIKE(c1, '[a-z]', 'c');Result:
Café café 1café eCafé James Bond 007