How can I search (case-insensitive) in a column us

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

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.

Answer 2

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

Answer 3

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 '%'

Answer 4

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

Answer 5

I'm doing something like that.

Getting the values in lowercase and MySQL does the rest

    $string = $_GET['string'];
    mysqli_query($con,"SELECT *
                       FROM table_name
                       WHERE LOWER(column_name)
                       LIKE LOWER('%$string%')");

And For MySQL PDO Alternative:

        $string = $_GET['string'];
        $q = "SELECT *
              FROM table_name
              WHERE LOWER(column_name)
              LIKE LOWER(?);";
        $query = $dbConnection->prepare($q);
        $query->bindValue(1, "%$string%", PDO::PARAM_STR);
        $query->execute();

Answer 6

You don't need to ALTER any table. Just use the following queries, prior to the actual SELECT query that you want to use the wildcard:

    set names `utf8`;
    SET COLLATION_CONNECTION=utf8_general_ci;
    SET CHARACTER_SET_CLIENT=utf8;
    SET CHARACTER_SET_RESULTS=utf8;