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();
up vote 2 down vote accepted

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();
  • 1
    Points! One corollary bit of info that came up. When doing a string comparison on a blob explicitly convert it to a string ala LOWER(CONVERT( @string_variable USING latin1)). Or whichever charset works for you. – ponies Jul 14 '15 at 19:20

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.