mySQL LIKE search is case sensitive?

icepig · 11-14-2003, 10:03 AM

I'm using MySQL 3.23.57. A query with LIKE seems making case sensitive search:
select desc from rental where desc LIKE '%eat-in-kitchen%';

will not match "Eat-in-Kitchen". This is diff from the mysql tutorial.

Any idea on how to make a case insensitive search with pattern match?

Thanks

Looking_Lost · 11-14-2003, 12:21 PM

Works fine for me in mysql 4

All I can think unless there's a difference in the versions is that the desc field as been defined with the BINARY attribute.

icepig · 11-14-2003, 01:39 PM

Thanks.

Also the query is made on BLOB field. Any way to get around it?

Looking_Lost · 11-14-2003, 03:29 PM

BLOB's are case sensitive by default so, you could convert it to lower-case

select desc from rental where LOWER(desc) LIKE '%eat-in-kitchen%';

or upper if you prefer

select desc from rental where UPPER(desc) LIKE '%EAT-IN-KITCHEN%';

which should pull out variations of eat-in-kitchen

icepig · 11-14-2003, 04:29 PM

That's a good point. Thanks