Обсуждение: how do I check for lower case

I have a table with a column, lets call it identifier, that is defined
as varchar(8) that should never contain lower case letters.  Its a large
table.  Is there a way to query the table to see if any values in this
column are lower case and to get a list out?  The user interface
application that users use prevents them from adding an entry in lower
case  now, but didn't in earlier version.

Julie

On 8/10/06, Juliann Meyer <Julie.Meyer@noaa.gov> wrote:
> I have a table with a column, lets call it identifier, that is defined
> as varchar(8) that should never contain lower case letters.  Its a large
> table.  Is there a way to query the table to see if any values in this
> column are lower case and to get a list out?  The user interface
> application that users use prevents them from adding an entry in lower
> case  now, but didn't in earlier version.

select * from sometable where identifier <> upper(identifier);

Regards,

Rodrigo

On 8/10/06 4:32 PM, "Juliann Meyer" <Julie.Meyer@noaa.gov> wrote:

> I have a table with a column, lets call it identifier, that is defined
> as varchar(8) that should never contain lower case letters.  Its a large
> table.  Is there a way to query the table to see if any values in this
> column are lower case and to get a list out?  The user interface
> application that users use prevents them from adding an entry in lower
> case  now, but didn't in earlier version.

select * from table where column ~ '[a-z]'

> 
> Julie
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

-- 
Daryl

"Hell, there are no rules here-- we're trying to accomplish something."   -- Thomas A. Edison

On Thu, 2006-08-10 at 15:32, Juliann Meyer wrote:
> I have a table with a column, lets call it identifier, that is defined 
> as varchar(8) that should never contain lower case letters.  Its a large 
> table.  Is there a way to query the table to see if any values in this 
> column are lower case and to get a list out?  The user interface 
> application that users use prevents them from adding an entry in lower 
> case  now, but didn't in earlier version. 

You can also use the same upper / lower functions to make sure no lower
case stuff gets into the table at a later date:

test=> create table aaa (a text check (upper(a)=a));
CREATE TABLE
test=> insert into aaa (a) values ('ABC');
INSERT 2120799293 1
test=> insert into aaa (a) values ('ABC123');
INSERT 2120799294 1
test=> insert into aaa (a) values ('ABC12a3');
ERROR:  new row for relation "aaa" violates check constraint "aaa_a"

viola!

Juliann Meyer wrote:
> I have a table with a column, lets call it identifier, that is defined 
> as varchar(8) that should never contain lower case letters.  Its a 
> large table.  Is there a way to query the table to see if any values 
> in this column are lower case and to get a list out?  The user 
> interface application that users use prevents them from adding an 
> entry in lower case  now, but didn't in earlier version.
> Julie
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>   

Not sure if  this is over simple  and perhaps this would be a concern on 
a very large table but 

select * from table where identifier <> upper(identifier);

would give a list of all where the entry is not all uppercase?

Oisin