Case Insensitive Like Searches In MySQL

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.

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

This will produce the following SQL query.

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

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

Comments

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

Permalink

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

Permalink

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
1 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.