pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow do I check for lower case
5+ messages / 5 participants
[nested] [flat]
* how do I check for lower case
@ 2006-08-10 20:32 Juliann Meyer <Julie.Meyer@noaa.gov>
0 siblings, 4 replies; 5+ messages in thread
From: Juliann Meyer @ 2006-08-10 20:32 UTC (permalink / raw)
To: pgsql-sql
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
Attachments:
julie.meyer.vcf (text/x-vcard, 247B) download
begin:vcard
fn:A. Juliann Meyer
org:National Weather Service;Missouri Basin River Forecast Center
adr;dom:;;;Pleasant Hill;MO;64080
email;internet:julie.meyer@noaa.gov
title:Sr. Hydrologist - Data Systems
x-mozilla-html:FALSE
version:2.1
end:vcard
* Re: how do I check for lower case
@ 2006-08-10 21:39 Rodrigo De León <rdeleonp@gmail.com>
parent: Juliann Meyer <Julie.Meyer@noaa.gov>
3 siblings, 0 replies; 5+ messages in thread
From: Rodrigo De León @ 2006-08-10 21:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how do I check for lower case
@ 2006-08-10 21:46 Daryl Richter <daryl@eddl.us>
parent: Juliann Meyer <Julie.Meyer@noaa.gov>
3 siblings, 0 replies; 5+ messages in thread
From: Daryl Richter @ 2006-08-10 21:46 UTC (permalink / raw)
To: Juliann Meyer <Julie.Meyer@noaa.gov>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how do I check for lower case
@ 2006-08-10 21:57 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Juliann Meyer <Julie.Meyer@noaa.gov>
3 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2006-08-10 21:57 UTC (permalink / raw)
To: Juliann Meyer <Julie.Meyer@noaa.gov>; +Cc: pgsql-sql
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how do I check for lower case
@ 2006-08-10 22:49 Oisin Glynn <me@oisinglynn.com>
parent: Juliann Meyer <Julie.Meyer@noaa.gov>
3 siblings, 0 replies; 5+ messages in thread
From: Oisin Glynn @ 2006-08-10 22:49 UTC (permalink / raw)
To: Juliann Meyer <Julie.Meyer@noaa.gov>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-08-10 22:49 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-10 20:32 how do I check for lower case Juliann Meyer <Julie.Meyer@noaa.gov>
2006-08-10 21:39 ` Rodrigo De León <rdeleonp@gmail.com>
2006-08-10 21:46 ` Daryl Richter <daryl@eddl.us>
2006-08-10 21:57 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-08-10 22:49 ` Oisin Glynn <me@oisinglynn.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox