Thread

1. Quickie

   pierre@desertmoon.com — 1999-02-08T21:57:22Z

   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
   
   

2. Re: [SQL] Quickie

   D'Arcy J.M. Cain <darcy@druid.net> — 1999-02-08T22:49:35Z

   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.