How to determine if a character is uppercase or lowercase in postgresql?

Question

I have failed to find any function like isupper or islower in postgresql. What I actually need is to select all the records from a table, where one of the columns contains capitized (but not uppercase) words. That is, the first symbol of each word is uppercase, and the second is lowercase. Words can be written in any language.

Answer 1

You can use Postgres regexp to test for your specific condition:

select * from sample 
where col ~ E'^[[:upper:]][^[:upper:]]'

You could use E'^[[:upper:]][[:lower:]]' if the second character must be lowercase alpha instead of any non-uppercase.

Answer 2

If you want to know whether a string contains at least one lower case character then you can use the upper function [upper(mystr)=mystr]:

dbname=> select upper('AAbbCC')='AAbbCC';
 ?column? 
----------
 f
(1 row)

dbname=> select upper('AABBCC')='AABBCC';
 ?column? 
----------
 t
(1 row)

You can use the same logic for checking that a string contains at least one upper case character with the lower() sql function.

For more complicated pattern, you will need to use regular expression or substring as proposed by earlier answers.