How to do case insensitive WHERE statement

Chronicle · 13 Dec 2006 at 19:06

Hello,
I need to do something like
SELECT * from table WHERE field = "whatever"
but it must also pick up all results that have field = WHATEVER, WhAtEvEr etc

How can i do case insensitive where search?
thanks

Beansprout · 13 Dec 2006 at 19:11

Ho hum, which system?

Chronicle · 13 Dec 2006 at 19:12

Oh. MySQL. forgot that lol...

riddlermarc · 13 Dec 2006 at 19:15

Have you tried what you posted?

B.5.1. Case Sensitivity in Searches

By default, MySQL searches are not case sensitive (although there are some character sets that are never case insensitive, such as czech). This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a.

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

Although BLOB fields are case sensitive, I believe.. but I doubt your data will be BLOBs?

Chronicle · 13 Dec 2006 at 20:25

Oh. Lol. I've always thought it was case sensitive. Didn't try it. Lol. Damn. Thanks.

Adz · 13 Dec 2006 at 20:42

I believe an = is case sensitive but if you use LIKE, it's insensitive. Therefore, using WHERE blah LIKE 'blah' (without any wildcards) rather than blah = 'blah' should give the desired result.

FerretBoy · 13 Dec 2006 at 21:02

Or

SELECT * from table WHERE Upper(field) = "WHATEVER"