How do you write a case insensitive query for both MySQL and Postgres?

There are several answers, none of which are very satisfactory.

* **LOWER(bar) = LOWER(?)** will *work* on MySQL and Postgres, but is likely to *perform terribly on MySQL*: MySQL won't use its indexes because of the LOWER function. On Postgres you can add a functional index (on **LOWER(bar)**) but MySQL doesn't support this.
* MySQL will (unless you have set a case-sensitive [collation][1]) do case-insensitive matching automatically, and use its indexes. (**bar = ?**).
* From your code outside the database, maintain **bar** and **bar_lower** fields, where bar_lower contains the result of **lower(bar)**. (This may be possible using database triggers, also). (See a discussion of this solution on [Drupal][2]). This is clumsy but does at least run the same way on pretty much every database.

[1]:

[To see links please register here]

[2]:

[To see links please register here]

REGEXP is case insensitive (unless used with BINARY), and can be used, like so...

SELECT id FROM person WHERE name REGEXP 'john';

...to match 'John', 'JOHN', 'john', etc.