Case-insensitive LIKE search

Anon

Hello... been working on a fairly basic PHP-based search that's using LIKE to find a string in a MySQL blob. However, the search seems to be case-sensitive, and I'm having a hard time figuring out how to turn it into a case-insensitive search... and help would be appreciated!

Anon

Use the UPPER function.

select * from table where UPPER(field) like UPPER('string');

You could use lower also. It doesn't really matter.

spider_man

You should also consider changing the data type to text, wich is case-insensitive by itself, wich optimizes the process.

Anon

Thanks! works perfect.

brian

smarlowe

Can you build an index on upper(textfield) in mysql? If so, then that would likely speed up the like searches as well.

i.e. something like:

create index table_upper on table (upper(fieldname));

would like speed up lookups, if the text type isn't already optimized to do with a regular index.