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.
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%'"
Depending on which character set you used to create the db, like can be case insensitive.