pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: [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