View Full Version : Ignore case when selecting

02-12-2007, 12:40 AM
Hey, I have a users DB, that contains a username and a password. If I have a user "User", I want them to be able to login using "user", or "UsEr" or anything...
Obviously I can set my $var to strtolower($var), but I dont know how to select in mysql and ignore the case.

02-12-2007, 02:01 AM
mysql is case insensitive by default.

02-12-2007, 02:15 AM
if the strtolower is active before the members join up, then they will be saved in lowercase, so if then u use strtolower when logging on it will set to lower case also..

the only problem is setting all the current members to lowerstring, maybe make some sort of script, depending on the size of the members list

02-12-2007, 03:49 AM
It doesn't matter if they are stored in lower, upper or mixed case. Mysql is case insensitive.

Dave, dave, DaVE, daVE, DavE are all equivalent and will all be found by:

from testtable
where name='DaVE'

so you don't need toupper, tolower or anything else.

02-12-2007, 01:12 PM
mysql is case insensitive by default.
Rofl... yeah, i misread your post, i thought you said Case Sensitive.. :D

Lord Emperor
02-12-2007, 08:08 PM
Sorry for going a bit offtopic, but what if I wanted it to be case-sensitive?

02-12-2007, 08:40 PM
From the mysql manual -
The BINARY operator casts the string following it to a binary string. This is an easy way to force a column comparison to be done byte by byte rather than character by character. This causes the comparison to be case sensitive even if the column isn't defined as BINARY or BLOB. BINARY also causes trailing spaces to be significant.

mysql> SELECT 'a' = 'A';
-> 1
mysql> SELECT BINARY 'a' = 'A';
-> 0
mysql> SELECT 'a' = 'a ';
-> 1
mysql> SELECT BINARY 'a' = 'a ';
-> 0