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

Question

I could not find any function like isupper or islower in postgresql. I really need to select all entries from a table where one of the columns contains capitulated (but not uppercase) words. That is, the first character of each word is uppercase, and the second is lowercase. Words can be written in any language.

Answer 1

How to simply select rows where the case of the first letter in the column is not equal to the lowercase version of the first letter in the column?

Something like:

 SELECT * FROM table WHERE SUBSTRING(col FROM 1 FOR 1) != LOWER(SUBSTRING(col FROM 1 FOR 1)) 

In theory, the above should also take into account the encoding / locale of the database.

Answer 2

You can use Postgres regexp to check your specific status:

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

You can use E'^[[:upper:]][[:lower:]]' if the second character should be a lowercase alpha instead of any uppercase.

Answer 3

Since postgresql case sensitive for string comparison, BobG's answer is better

Another solution would be to use ascii with string functions

Like this

 SELECT * FROM yourTable WHERE (ascii(LEFT(yourColumn), 1) BETWEEN 65 AND 90) AND (ascii(SUBSTRING(yourColumn from 2 for 1), 1) BETWEEN 97 AND 122) 

when between 65 and 90 it is a capital letter, as you can see in the ascii table, I linked

if between 97 and 122 it is lowercase

Answer 4

If you want to know if a string contains at least one lowercase character, 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 to verify that a string contains at least one uppercase character with a lower () sql function.

For a more complex pattern, you will need to use a regular expression or substring, as suggested by earlier answers.