How can I search (case-insensitive) in a column using LIKE wildcard?

Question

I looked around some and didn't find what I was after so here goes.

SELECT * FROM trees WHERE trees.`title` LIKE  '%elm%'

This works fine, but not if the tree is named Elm or ELM etc...

How do I make SQL case insensitive for this wild-card search?

I'm using MySQL 5 and Apache.

Answer 1

Try the code given below:

SELECT*

FROM trees

WHERE trees.`title` COLLATE UTF8_GENERAL_CI LIKE '%elm%'

If you are adding COLLATE UTF8_GENERAL_CI to your column's definition, then you can make SQL case insensitive.

ALTER TABLE trees 

MODIFY COLUMN title VARCHAR(…) CHARACTER

SET UTF8 COLLATE UTF8_GENERAL_CI.

The above code will also rebuild any indexes on the column hence, they can be used for the queries without leading '%'.