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

mysql sql sql-like case-insensitive
David Morrow picture David Morrow · May 20, 2010 · Viewed 354.5k times · Source

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

Quassnoi picture Quassnoi · May 20, 2010 
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 '%'

Related questions

SQL SELECT LIKE (Insensitive casing)

I am trying to execute the sql query: select * from table where column like '%value%'; But the data is saved as 'Value' ( V is capital ). When I execute this query i don't get any rows. How do i …

Read More
MySQL Like multiple values

I have this MySQL query. I have database fields with this contents sports,shopping,pool,pc,games shopping,pool,pc,games sports,pub,swimming, pool, pc, games Why does this like query does not work? I need the fields with …

Read More
Use LIKE %..% with field values in MySQL

I stumbled into a delicate SQL problem when I needed to use a value from a field inside a LIKE %..% statement. Example: SELECT t1.Notes, t2.Name FROM Table1 t1, Table2 t2 WHERE t1.Notes LIKE '%t2.Name%' …

Read More