3 Ways to Find Rows that Contain Uppercase Letters in SQLite

Posted on by Ian

Here are three options for returning rows that contain uppercase characters in SQLite.

Sample Data

Suppose we have a table with the following data:

.nullvalue null
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 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:

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

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).

SQLite performs a case-sensitive search, and so we don’t need to do anything else to get the result we desire.

You might have noticed that the above example didn’t return the uppercase unicode characters É and Ø unless the value also included a non-unicode uppercase letter. SQLite does not fully support unicode characters. For example, the LOWER() and UPPER() functions only provide case mapping for the 26 letters used in the English language. Therefore, the above example didn’t convert any unicode characters to their lowercase equivalent.

Take a look at the SQLite ICU extension if you need to work with unicode characters.

Option 2: 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 c1 REGEXP '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]';

Result:

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

We can include unicode characters in our list if we wish:

SELECT c1 FROM t1
WHERE c1 REGEXP '[ÉØABCDEFGHIJKLMNOPQRSTUVWXYZ]';

Result:

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

Option 3: 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 c1 REGEXP '[A-Z]';

Result:

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