Convenient One-shot Way To Select Value Irrespective Of Case

[chibineku]

I want to allow users to register with their username/email address in any combination of upper and lower case characters they like, but to check at registration, login and account management instances that no e-mail address with the same username/email address exists irrespective of case. I am aware that I can use LOWER() in mysql queries, but if I tried, for example:"SELECT id FROM aromaMaster WHERE email = '".strtolower($email)."'";that would only work if I did strtolower on insertion, right? Can I make the WHERE email clause case insensitive?

[justsomeguy]

"SELECT id FROM aromaMaster WHERE LOWER(email) = '".strtolower($email)."'";

[chibineku]

That was what I thought, but I just figured that would never work - it's never that easy. I am really impressed overall with the thought that the developers of MySQL put into it - there is a simple way of doing everything. Just consider the simple logic of ON DUPLICATE KEY UPDATE for an example.

[justsomeguy]

Right. Most of that isn't specific to MySQL, it's part of the official SQL spec.