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    trees
WHERE   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 trees
WHERE trees.`title` LIKE '%elm%' COLLATE utf8_general_ci

for instance.

(Replace utf8_general_ci with whatever collation you find useful). The _ci stands for case insensitive.

This is the example of a simple LIKE query:

SELECT * FROM <table> WHERE <key> LIKE '%<searchpattern>%'

Now, case-insensitive using LOWER() func:

SELECT * FROM <table> WHERE LOWER(<key>) LIKE LOWER('%<searchpattern>%')

Simply use :

"SELECT * FROM `trees` WHERE LOWER(trees.`title`) LIKE  '%elm%'";

Or Use

"SELECT * FROM `trees` WHERE LCASE(trees.`title`) LIKE  '%elm%'";

Both functions works same