How can I make db_query SELECT LIKE case insensitive?

Question

I'm querying for an email address where I can't force the case of the search term or the data in the table. It's an API thing.

How can I convert this query to be case insensitive?

$results = db_query(
 'SELECT email_address
  FROM email_address_table
  WHERE (email_address LIKE :mail)',
  array(':mail' => '%' . db_like($mail) . '%')
)->fetchAll();

Answer 1

The typical way to accomplish this is to compare a lower-case version of you search string against a the lower-cased variant in the table. See below for an example using strtolower() and LOWER():

$mail = strtolower($mail);

$results = db_query(
 'SELECT email_address
  FROM email_address_table
  WHERE (LOWER(email_address) LIKE :mail)',
  array(':mail' => '%' . db_like($mail) . '%')
)->fetchAll();