CODEWITHSUNDEEP
mysqlsqlsql-likecase-insensitive
David Morrow
David Morrow

Reputation: 9354

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

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.

Upvotes: 451

Views: 600385

Answers (16)

Muhammad Bilal
Muhammad Bilal

Reputation: 3008

Try this it worked for me.

SELECT * FROM trees WHERE trees.`title` ILIKE '%elm%';

Upvotes: -2

Shadi
Shadi

Reputation: 175

I've always solved like this:

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

Upvotes: 4

Marko Bonaci
Marko Bonaci

Reputation: 5708

Non-binary string comparisons (including LIKE) are case insensitive by default in MySql: https://dev.mysql.com/doc/refman/en/case-sensitivity.html

Upvotes: 12

ABS zarzis
ABS zarzis

Reputation: 177

use ILIKE

SELECT * FROM trees WHERE trees.`title` ILIKE '%elm%';

it worked for me !!

Upvotes: 8

Vi8L
Vi8L

Reputation: 988

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

Upvotes: 51

Lopofsky
Lopofsky

Reputation: 528

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;

Upvotes: 6

Quassnoi
Quassnoi

Reputation: 425251

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

Upvotes: 337

Dave Doga Oz
Dave Doga Oz

Reputation: 1268

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();

Upvotes: 17

user4189641
user4189641

Reputation: 21

SELECT name 
       FROM gallery 
       WHERE CONVERT(name USING utf8) LIKE _utf8 '%$q%' 
       GROUP BY name COLLATE utf8_general_ci LIMIT 5

Upvotes: 2

Federico Piragua
Federico Piragua

Reputation: 639

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

Upvotes: 60

aioobe
aioobe

Reputation: 420921

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.

Upvotes: 54

simplifiedDB
simplifiedDB

Reputation: 164

well in mysql 5.5 , like operator is insensitive...so if your vale is elm or ELM or Elm or eLM or any other , and you use like '%elm%' , it will list all the matching values.

I cant say about earlier versions of mysql.

If you go in Oracle , like work as case-sensitive , so if you type like '%elm%' , it will go only for this and ignore uppercases..

Strange , but this is how it is :)

Upvotes: 3

cgupta
cgupta

Reputation: 307

I think this query will do a case insensitive search:

SELECT * FROM trees WHERE trees.`title` ILIKE '%elm%';

Upvotes: 8

Rbacarin
Rbacarin

Reputation: 707

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

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157828

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

Upvotes: 1

cwallenpoole
cwallenpoole

Reputation: 81988

I've always solved this using lower:

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

Upvotes: 468

Related Questions