How can I search (case-insensitive) in a column using LIKE wildcard?
I've always solved this using lower:
SELECT * FROM trees WHERE LOWER( trees.title ) LIKE '%elm%'
SELECT *FROM treesWHERE trees.`title` COLLATE UTF8_GENERAL_CI LIKE '%elm%'Actually, if you add COLLATE UTF8_GENERAL_CI to your column's definition, you can just omit all these tricks: it will work automatically.
ALTER TABLE trees MODIFY COLUMN title VARCHAR(…) CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI. This will also rebuild any indexes on this column so that they could be used for the queries without leading '%'
The case sensitivity is defined in the columns / tables / database collation settings. You can do the query under a specific collation in the following way:
SELECT *FROM treesWHERE trees.`title` LIKE '%elm%' COLLATE utf8_general_cifor instance.
(Replace utf8_general_ci with whatever collation you find useful). The _ci stands for case insensitive.