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.
BobG :
What about just selecting the 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?\n\nSomething like:\n\nSELECT * FROM table \n WHERE SUBSTRING(col FROM 1 FOR 1) != LOWER(SUBSTRING(col FROM 1 FOR 1))\n\n\nIn theory, the above should take the database charset/locale into account as well.",
2012-12-04T16:38:35
dbenhur :
You can use Postgres regexp to test for your specific condition:\n\nselect * from sample \nwhere col ~ E'^[[:upper:]][^[:upper:]]'\n\n\nYou could use E'^[[:upper:]][[:lower:]]' if the second character must be lowercase alpha instead of any non-uppercase.",
2012-12-04T17:40:09
Kemin Zhou :
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]:\n\ndbname=> select upper('AAbbCC')='AAbbCC';\n ?column? \n----------\n f\n(1 row)\n\ndbname=> select upper('AABBCC')='AABBCC';\n ?column? \n----------\n t\n(1 row)\n\n\nYou can use the same logic for checking that a string contains at least one upper case character with the lower() sql function. \n\nFor more complicated pattern, you will need to use regular expression or substring as proposed by earlier answers.",
2018-01-05T06:02:22