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.

Firebrand

If you stumbled on this page but your MySql settings DO work on this query for Elm or ELM and you WANT it to be case sensitive, see BINARY such as here: #7858169 – Aspiration

Isn't it depend on field/table collation? like 'ut8_general_CI' – Jubilant

MySQL's like should be case-insensitive by default. – Orion

Answer 1

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

Omophagia

that works too, is this more correct way of doing things? there is a possibility this will be localized so is using the encoding better than lower? – Firebrand

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 '%'. – Omophagia

ALTER TABLE trees MODIFY COLUMN title VARCHAR(…) this seems the best way, thanks much... let sql do the work – Firebrand

Friendly reminder that this is a mysql answer. If you're using PostgreSQL, ILike is the solution to the above question. – Tugman

latin1_general_ci for latin1 see dev.mysql.com/doc/refman/5.0/en/charset-mysql.html – Whyte

This is correct answer as long, as your original collate was general, not bin. – Lash

How if latin1_general_ci ?, this work or not ? – Epidemic

@MPancadewa: I'm not quite sure what it is your asking, but _ci in the collation name means "case insensitive". – Omophagia

Unfortunately, this does not work for binary data (e.g. blob with serialized data). – Alcazar

IMPORTANT MySQL's UTF8 is NOT UTF8. It's a broken, MySQL proprietary encoding that has max 3 bytes. Real UTF8 supports up to 4 bytes. Do NOT use any MySQL 'UTF8' encodings or collations ever! Instead, use UTF8MB4, which MySQL added later to represent the encoding the rest of the world calls UTF8. Confusing, I know. But this WILL come to haunt you! Research this or be bitten by it. Quick test: Try to insert an emoji like ❤️. If you use MySQL UTF8 it will fail. If you use MySQL UTF8MB4 it works as intended. – Quicken

In addition to @StijndeWitt comment: here is the related discussion on this subject that provide lots of important details: What's the difference between utf8_general_ci and utf8_unicode_ci? – Xylene

I doubt it, as both utf8_general_ci and utf8_unicode_ci are broken. As their prefix suggests, they are both collations for the same, broken, utf8 charset, which encodes max. 3 bytes. Collations are important. They specify sorting/ordering behavior. But they are based on a charset and in case of MySQL utf8 means broken. Use utf8mb4 for full Unicode support. And that means use utf8mb4_unicode_ci as well. – Quicken

Answer 2

I've always solved this using lower:

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

Menander

Does MySQL 5 have an ILIKE operator? – Oestrin

though for the %% search it doesn't matter anyway :) – Alteration

@Luke Mysql don't need it. As it's collation responsibility. – Alteration

(Wait, I thought everything in MySQL is case insensitive? Or is string equality case insensitive but matching case sensitive?!) – Oestrin

(Ah. Okay, back to things I actually know anything about :-) ) – Oestrin

@Col. -- Admittedly, this is less than ideal for indexed columns, but it will work for a structure which is already in place. I've also found that case-insensitive searches are more often on columns which are not indexed anyway. – Menander

perfect! exactly want i wanted, i knew sql would have something like that, just didnt know. many thanks... – Firebrand

Default collation is already CI. So, the real problem not in this particular question. But it's still perfect SO-style answer. – Alteration

Exactly what I wanted as well. Which trumps spoiling index use :) – Sprite

Concerning Indexes, if you you really need to optimize a case-insensitive search, then you would have an extra column on your table, which contains the target field ALREADY mapped to lowercase (or uppercase), then you would create an index on that column. The downside is that you now have a larger DB which is not normalized. But your searches are fast. – Nasturtium

what makes you think the data will be minor case, this is less than ideal solution – Hanson

I just tried this using LOWER like this was incredibly slow went from almost no time to 0.5 seconds... – Debatable

Unfortunately, this does not work for binary data (e.g. blob with serialized data). – Alcazar

UPPER and LOWER have other problems as well... Read this entertaining post by SO legend Jon Skeet for more info: codeblog.jonskeet.uk/2009/11/02/… (Search for 'Turkey' to get to the part where he discusses issues with UPPER/LOWER) – Quicken

Answer 3

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

Longlived

Actually this is a pretty nice solution especially when you are faced with COLLATE format issues – Gallinacean

Great! I was about to write this answer :) It is important to use the same function on both sides, since collation make break things, so this assures, both sides are processed in the same manner (it does not matter how, it is only important that both are the same!) – Belda

Answer 4

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.

Vaccinate

In MySQL 5.6 I get ERROR 1273 (HY000): Unknown collation: 'utf_general_ci'. I'd guess this collation has been removed from MySQL? utf8_general_ci works fine, though. – Irradiate

Had the same issue. You either have to fix your COLLATE or do a simple trick like this one(LOWER() both of your strings before comparison) – Gallinacean

In MySQL 5.6+ or MariaDB 10+ you just need to supply COLLATE instruction before your condition. So this works: SELECT * FROM products WHERE name COLLATE utf8_general_ci LIKE 'AB47TU'; – Natishanative

Answer 5

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

Beardsley

Answer 6

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

Ragouzis

Answer 7

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

Orion

Answer 8

use ILIKE

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

it worked for me !!

Wispy

MySQL does not support ILIKE. – Mediatorial

That works for PostgreSQL, the question was regarding MySQL. – Lomax

Answer 9

I think this query will do a case insensitive search:

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

Endmost

I get syntax error on mysql 5.5 while using ILIKE in my queries – Angeli

This works only for PostgreSQL; not MySQL. postgresql.org/docs/current/static/functions-matching.html – Clercq

As noted already, the question was about MySQL and the answer is about PostgreSQL and sure as hell doesn't work with MySQL. I don't down-vote it but can't help wondering where the up-votes come from... – Maillot

Answer 10

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;

Creel

This is a very underrated comment. It addresses the question most generally. I do think the alter table syntax is important too, as the question may want the comparison limited to only that one column. – Lesterlesya

Answer 11

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 :)

Firehouse

This isn't entirely true. It works that way only if the collation is set to *_ci, which stands for "case insensitive". As this happens to be default for all supported character sets (issue show character set; to check this) - the answer is partially true :-) Only the reason is incorrect. It is not the operator that is case insensitive, it is the default collation that is. – Maillot

yes m agree with you . It depends on character and still if you are in production with *_ci character then only option is to use binary before where clause – Firehouse

Answer 12

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

Pentalpha

gallery is the table-name , name is the column in the table , – Pentalpha

please add some explnataion of your code showing what it does and how it helps - this will help others in the future – Shirashirah

Answer 13

I've always solved like this:

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

Hydrolytic

Answer 14

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

Alteration

Answer 15

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

Phipps

Answer 16

For example if you want to search name like Raja not raja, Royal not royal etc, add BINARY before column name in WHERE clause.

SELECT name FROM person_tbl
WHERE BINARY name LIKE "R%";

Pulpy