Here are four options for returning rows that contain uppercase 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 [:upper:] POSIX character class to check for uppercase characters:
SELECT c1 FROM t1
WHERE REGEXP_LIKE(c1, '[[:upper:]]');Result:
CAFÉ Café eCafé James Bond 007 JB 007 É É 123 Ø
Option 2: Compare to the LOWER() String
We can use the LOWER() function to compare the original value to its lowercase equivalent:
SELECT c1 FROM t1
WHERE LOWER(c1) <> c1;Result:
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, 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 uppercase character we want to match:
SELECT c1 FROM t1
WHERE REGEXP_LIKE(c1, '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]', 'c');Result:
CAFÉ Café eCafé James Bond 007 JB 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 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.
Here it is again with those two characters included:
SELECT c1 FROM t1
WHERE REGEXP_LIKE(c1, '[ÉØABCDEFGHIJKLMNOPQRSTUVWXYZ]', 'c');Result:
CAFÉ Café eCafé James Bond 007 JB 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é eCafé James Bond 007 JB 007