Re: Best way to store case-insensitive data?

Heyho!

On Thursday 10 June 2010 22.50:23 Mike Christensen wrote:
> 2) Every time the user updates or saves their email, store it in
> lowercase, and every time I lookup an email, pass in a lowercase
> email.

I'd do it this way.  Plus either a CHECK condition on the table (email =
lowercase(email)) (this will reliably catch all cases, but you will
experience failures until you have found all cases)

Or a BEFORE trigger that converts email to lowercase.  (This is mostly
transparent for storing, but I usually try to avoid triggers that modify
data like this.  But that's probably just me.)

In either case, obviously you'll still need to change the code that is used
for retrieving and comparing email addresses.

cheers
-- vbi

--
featured link: http://www.pool.ntp.org