case insensitive for sql LIKE wildcard statement

Question

Sorry if this is a repeat. 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 ect... How do I make SQL case insensitive for this wild-card search?

Again, apologies if this is repeated.

Oh, using MySql 5 on apache.

Answer 1

I've always solved this using lower:

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

Answer 2

You must set up proper encoding and collation for your tables.

Table encoding must reflect the actual data encoding. What is your data encoding?

To see table encoding, you can run a query SHOW CREATE TABLE tablename

Answer 3

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 utf_general_ci

for instance.

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

Answer 4

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

Answer 5

When I want to develop insensitive case searchs, I always convert every string to lower case before do comparasion