CODEWITHSUNDEEP
postgresqluppercaselowercase
Pupkov-Zadnij
Pupkov-Zadnij

Reputation: 1392

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

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.

Upvotes: 16

Views: 36666

Answers (4)

Kemin Zhou
Kemin Zhou

Reputation: 6881

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.

Upvotes: 6

dbenhur
dbenhur

Reputation: 20408

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.

Upvotes: 17

Marc
Marc

Reputation: 16512

Since postgresql is case sensitive for string comparisons, BobG 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 it's between 65 and 90 it's a capital letter as you can see in the ascii table I linked

if it's between 97 and 122 it's lower case

Upvotes: 1

BobG
BobG

Reputation: 2171

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?

Something like:

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

In theory, the above should take the database charset/locale into account as well.

Upvotes: 38

Related Questions