php/mysql: how to search case insentitively

Question

i have this table in UTF8 (collation is utf8_bin in case it matters).

I need to search in it for strings using LIKE '%$searchstring%' .

Right now you have to use the exact case in order for the results to show up. Is there a way to make it case insensitive ?

Thank you

UPDATE sorry, found the answer.

score 2 · Accepted Answer · answered Oct 30 '09 at 19:30

2

Use:

WHERE LOWER(t.column) LIKE LOWER('%search_string%')

..or:

WHERE UPPER(t.column) LIKE UPPER('%search_string%')

References:

answered Oct 30 '09 at 19:30

OMG Ponies

according to http://stackoverflow.com/questions/234591/upper-vs-lower-case/234751#234751, comparison with 'lower' or 'upper' can be incorrect for some languages – akavel Nov 10 '09 at 11:10
What about `... (etc COLLATE utf8__ci) LIKE '%searchstring%'`? By the way, if you want "Çilek" to show up in results when the user searches for "cilek", you can use `utf8_unicode_ci`, that is, both case insensitive and collation insensitive search. – Halil Özgür Jun 17 '11 at 08:42

score 0 · Answer 2 · answered Oct 30 '09 at 19:29

0

Your collation specifies the case-sensitivity. Use this instead:

utf8_ci

answered Oct 30 '09 at 19:29

AJ.

2 Answers2