Thread: Selecting all records which are in upper case
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
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
On Sat, Aug 27, 2011 at 03:12:44PM +0530, Amitabh Kant wrote: > 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: Might not work if you have non-ascii characters (but your example code breaks there too), but what about: DELETE ... WHERE upper(name) = name; Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
Attachment
On Sat, Aug 27, 2011 at 3:40 PM, Martijn van Oosterhout <kleptog@svana.org> wrote:
On Sat, Aug 27, 2011 at 03:12:44PM +0530, Amitabh Kant wrote:Might not work if you have non-ascii characters (but your example code
> 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:
breaks there too), but what about:
DELETE ... WHERE upper(name) = name;
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Thanks Martin. Definitely a much simpler way. I also cross-checked it on my table, and it does work on non-ascii characters. It is only returning me upper case entries. All other entries remain unaffected.
Amitabh