pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] Quickie
3+ messages / 3 participants
[nested] [flat]
* Re: [SQL] Quickie
@ 1999-02-08 21:56 Gregory W Burnham <gburnham@sfu.ca>
0 siblings, 0 replies; 3+ messages in thread
From: Gregory W Burnham @ 1999-02-08 21:56 UTC (permalink / raw)
To: pierre@desertmoon.com; pgsql-sql
select User_Email from <whatever your table name is> where User_Email ~*
'fubar';
I think, I haven't tried it yet.
Gregory W Burnham
Software Engineer
Excite Labs
Faculty Of Education
Simon Fraser University
Vancouver, B.C.
(604) 291 3615 ph
(604) 291 5679 fx
----- Original Message -----
From: <pierre@desertmoon.com>
To: <pgsql-sql@postgreSQL.org>
Sent: Monday, February 08, 1999 1:57 PM
Subject: [SQL] Quickie
>Hi all...here is a quickie for ya:
>
>Given:
>User_Email|User_ID
>-------------------
>fubar | 1
>barfu | 2
>snafu | 3
>Fubar | 4
>
>What query could I use to return ONLY 'fubar' and 'Fubar'? Would I do a
self
>join? The problem is that I'd forgotten to force everything to lowercase
upon
>insertion, and now I need to clean out the uppercase records and/or convert
them
>to lowercase.
>
>Ideas?
>
>-=pierre
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Quickie
@ 1999-02-08 21:57 pierre@desertmoon.com
1999-02-08 22:49 ` Re: [SQL] Quickie D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 1 reply; 3+ messages in thread
From: pierre@desertmoon.com @ 1999-02-08 21:57 UTC (permalink / raw)
To: pgsql-sql
Hi all...here is a quickie for ya:
Given:
User_Email|User_ID
-------------------
fubar | 1
barfu | 2
snafu | 3
Fubar | 4
What query could I use to return ONLY 'fubar' and 'Fubar'? Would I do a self
join? The problem is that I'd forgotten to force everything to lowercase upon
insertion, and now I need to clean out the uppercase records and/or convert them
to lowercase.
Ideas?
-=pierre
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Quickie
1999-02-08 21:57 Quickie pierre@desertmoon.com
@ 1999-02-08 22:49 ` D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 0 replies; 3+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-02-08 22:49 UTC (permalink / raw)
To: pierre@desertmoon.com; +Cc: pgsql-sql
Thus spake pierre@desertmoon.com
> Given:
> User_Email|User_ID
> -------------------
> fubar | 1
> barfu | 2
> snafu | 3
> Fubar | 4
>
> What query could I use to return ONLY 'fubar' and 'Fubar'? Would I do a self
Well, the obvious is;
SELECT * FROM x WHERE User_Email = 'fubar' OR User_Email = 'Fubar';
But I assume you are looking for something more useful. Are you trying
to catch all entries not in all lower case? Try this:
SELECT * FROM x WHERE User_Email != lower(User_Email);
Or you can convert to lower with this:
UPDATE x SET User_Email = LOWER(User_Email);
If you have a unique index on User_Email then the ones that already
exist will fail and then you can use the first one to find the ones
that didn't get converted. Those will be the dups.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-02-08 22:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-08 21:56 Re: [SQL] Quickie Gregory W Burnham <gburnham@sfu.ca>
1999-02-08 21:57 Quickie pierre@desertmoon.com
1999-02-08 22:49 ` D'Arcy J.M. Cain <darcy@druid.net>
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