Case Sensitivity with MySQL

Hmmmmm....

[Nick_W]

er....

I didn't know that MySQL was NOT case sensitive. In fact, I'm sure I just need telling what I'm doing wrong...

I need a field to be case sensitive.

At the moment it's a VARCHAR(20) and when it contains 'nick' it will return rows with queries like 'NICK' and 'NiCk'.

WHat area controls this?

Many thanks...

Nick

[jatar_k]

are you using a LIKE search? or what is the actual search query you are using?

[Nick_W]

Here it is....

$qry="SELECT uname FROM $table WHERE uname='$uname'";

Nick

[jatar_k]

Looks like you need to mess with BINARY

Case Sensitivity Operators [mysql.com]
Case Sensitivity [mysql.com]

from the above link

By default, MySQL searches are case-insensitive (although there are some character sets that are never case-insensitive, such as czech). That means that if you search with col_name LIKE 'a%', you will get all column values that start with A or a. If you want to make this search case-sensitive, use something like INSTR(col_name, "A")=1 to check a prefix. Or use STRCMP(col_name, "A") = 0 if the column value must be exactly "A".

[Nick_W]

Hmmm... Thanks. I thought that was only for LIKE stuff...

Nick

[jatar_k]

So did I actually.

[lorax]

Umm.. just so we don't confuse newbees - references to table names ARE case-sensitive with MySQL.

I just tested the case sensitivity of the db and field names and they didn't seem to make a difference - though I thought they would.

[jatar_k]

good point lorax, for case insensitivity we were referring only to searches.