Case insensitive search?

[Anon]

How do you do a case insensitive search in MySQL?

I am using this code

$query ="SELECT * FROM Database WHERE Username='" . $HTTP_POST_VARS[username] . "'" ;

As it stands if the user enters in Joe it will not match something like joe or JOE with the select.

Any help would be appreciated

[saloon12yrd]

Use a TEXT field instead of BLOB. Or RTFM.

Greets,

Dominique

[steved]

$query ="SELECT * FROM Database WHERE lower(Username) = '" . strtolower($HTTP_POST_VARS[username]) . "'" ;

[Anon]

While, Steve's suggestion will work, it would be easier (and more efficient) if you switch your field to a non-binary field. i.e.:
char or varchar instead of char binary, varchar binary
or
text instead of blob

Although I hope you aren't using a blob to hold a username (one wouldn't expect a username to exceed 255 characters...)

[Anon]

Thanks 🙂

[Anon]

Note that will cause a table scan since lower( something ) will not use any indexes on 'something'.

[steved]

but, it will use an index that you have setup on lower(something).

[mattr]

MySQL doesn't support function-based indexes, do they?