15

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.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Pupkov-Zadnij
  • 1,342
  • 2
  • 11
  • 21

4 Answers4

36

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.

BobG
  • 2,113
  • 17
  • 15
  • @Marc: Maybe I'm misreading your comment, but when I posted the SELECT, I tested the syntax in MySQL instead of Postgres since I didn't have a Postgres instance handy (I already knew the LOWER/UPPER/SUBSTRING functions were valid in Postgres). It worked just fine in MySQL (v 5.1.something), successfully returning a row that contained 'Abc' but not the row containing 'xyz.' – BobG Dec 05 '12 at 17:06
  • Sorry, I wasn't clear. That depends of your collation.By default it's Latin1 and latin1_swedish_ci which are case insensitive. http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html – Marc Dec 05 '12 at 17:17
17

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.

dbenhur
  • 20,008
  • 4
  • 48
  • 45
5

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.

Kemin Zhou
  • 6,264
  • 2
  • 48
  • 56
1

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

Marc
  • 16,170
  • 20
  • 76
  • 119