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

use ILIKE

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

it worked for me !!

Answer 2

I've always solved this using lower:

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

Answer 3

I think this query will do a case insensitive search:

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

Answer 4

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 5

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

Answer 6

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