Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Quickie

From: "D'Arcy" "J(dot)M(dot)" Cain <darcy(at)druid(dot)net>
To: pierre(at)desertmoon(dot)com
Cc: pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] Quickie
Date: 1999-02-08 22:49:35
Message-ID: m109zUm-0000bNC@druid.net (view raw or flat)
Thread:
Lists: pgsql-sql
Thus spake pierre(at)desertmoon(dot)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(at){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.

In response to

  • Quickie at 1999-02-08 21:57:22 from pierre

pgsql-sql by date

Next:From: Oliver ElphickDate: 1999-02-09 00:03:06
Subject: Re: [SQL] HAVING
Previous:From: pierreDate: 1999-02-08 21:57:22
Subject: Quickie

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group