Selecting all records which are in upper case

From: Amitabh Kant <amitabhkant(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Selecting all records which are in upper case
Date: 2011-08-27 09:42:44
Message-ID: CAPTAQB+DBM4BRi0Z45SmEWm83PwF-frXm=_mGONujgFAGgNM4A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello

I have a simple table 'location' :
id -> Int (associated with a sequence)
name -> Character varying (100)

I have to delete all records where values in name field are all in upper
case. For example, if the test data is as follows:

id name
1 abccvvvv
2 Abc dsase
3 CDF FDER
4 Amcddd FFR
5 EE DFEW
6 Sedcd

Only reecords #3 and #5 are to be deleted. The closest I could reach was
this:
"delete from location where (ascii(substring(name from 1 for 1)) between 65
and 90) and (ascii(substring(name from char_length(name) for 1)) between 65
and 90)"
The problem with this query is it would also delete record #4.

How do I get it to select only those records in which all characters are in
uppercase?

Amitabh

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2011-08-27 10:10:18 Re: Selecting all records which are in upper case
Previous Message pasman pasmański 2011-08-27 05:19:59 Re: Need help with dropping a view please