Thread: 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
Attachment
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