Case Insensitive Like Searches In MySQL

2nd July 2011

I needed to create a query that did a case insensitive search using the LIKE command in MySQL and I quickly realised that in order to do this I would need to alter both the parameter and the table data to be the same case. This can be done by using the MySQL UPPER() command on the table data and the strtoupper() PHP function on the input data.

  1. $name = strtoupper('phil');
  2. $query = "SELECT * FROM users WHERE UPPER(forename) LIKE '" . $name . "%'";

This will produce the following SQL query.

  1. SELECT * FROM users WHERE UPPER(forename) LIKE 'P%'

I hope that little tip comes in handy to someone looking for the same solution.

Comments

Permalink

LIKE in mysql is always case insensitive but for non ascii characters you must use the right charset and collation on your tables.

Test it with "SELECT 'foobar' LIKE 'FOO%'"

Blar (Thu, 09/08/2011 - 14:23)

Permalink

Depending on which character set you used to create the db, like can be case insensitive.

Marc Johnen (Fri, 04/13/2012 - 17:05)

Add new comment

The content of this field is kept private and will not be shown publicly.