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

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.