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

Question

I run the MySQL database locally for development, but deploy to Heroku, which uses Postgres. Heroku handles almost everything, but my case is not case sensitive. Such statements become case sensitive. I could use iLike instructions, but my local MySQL database cannot handle this.

What is the best way to write a case-insensitive query compatible with MySQL and Postgres? Or do I need to write separate Like and iLike instructions depending on what my application is connected with?

Answer 1

select * from foo where upper(bar) = upper(?); 

If you set the parameter to uppercase in the caller, you can avoid the second function call.

Answer 2

The moral of this story: Do not use a different software stack for development and production. Never.

As a result, you will have errors that you cannot reproduce in dev; your testing will be futile. Just don't do it.

Using another database mechanism is out of the question - there will be FAR more cases when it behaves differently than just LIKE (also you checked the mappings used by the databases, are they identical in EVERY case ?, you can forget ORDER BY on the columns varchar working the same)

Answer 3

Use Arel:

 Author.where(Author.arel_table[:name].matches("%foo%")) 

matches will use the ILIKE operator for Postgres and LIKE for everything else.

Answer 4

In postgres you can do this:

 SELECT whatever FROM mytable WHERE something ILIKE 'match this'; 

I'm not sure if there is an equivalent for MySQL, but you can always do this, which is a bit ugly, but should work in both MySQL and postgres:

 SELECT whatever FROM mytable WHERE UPPER(something) = UPPER('match this'); 

Answer 5

There are several answers, none of which are satisfactory.

• LOWER (bar) = LOWER (?) Will work on MySQL and Postgres, but it will probably work terribly on MySQL: MySQL will not use its indexes because of the LOWER function. In Postgres you can add a functional index (on LOWER (bar) ), but MySQL does not support this.
• MySQL will (if you did not specify an account ) , not case-sensitive, case-insensitive and uses its indexes. ( bar =? ).
• From your code outside the database, support the bar and bar_lower fields , where bar_lower contains the result below (bar) . (This may be possible using database triggers). (See a discussion of this solution on Drupal .) This is awkward, but at least it works the same in every database.

Answer 6

REGEXP is case insensitive (if not used with BINARY), and can be used, for example ...

  SELECT id FROM person WHERE name REGEXP 'john'; 

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

Answer 7

If you are using PostgreSQL 8.4, you can use the citext module to create case-insensitive text fields.

Answer 8

use COLLATE.

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

Answer 9

You may also consider looking at the searchlogic plugin that runs LIKE / ILIKE for you.

Answer 10

You can also use ~ * in postgres if you want to match a substring inside a block. ~ matches case-sensitive substring, ~ * case-insensitive substring. This is a slow operation, but I can find it useful for searches.

 Select * from table where column ~* 'UnEvEn TeXt'; Select * from table where column ~ 'Uneven text'; 

Both attacked "Some Uneven text here" Only the first would click on "Some UNEVEN TEXT here"

Answer 11

Converted to the top one is best since it covers compatible syntax for the 3 most used Rails database backends. PostgreSQL, MySQL, and SQLite support this syntax. This has a (minor) flaw that you have to make your search bar in your application or in the conditions bar to make it a little ugly, but I think the compatibility you are typing makes it worthy.

Both MySQL and SQLite3 have a case-insensitive LIKE statement. Only PostgreSQL has a case-sensitive LIKE operator and PostgreSQL-specific (for guidance) ILIKE case-insensitive search. You can specify ILIKE insead LIKE in your conditions in the Rails application, but keep in mind that the application will stop working under MySQL or SQLite.

A third option might be to check which database engine you are using and modify the search bar accordingly. This can be better done by breaking into / monkeypatching the ActiveRecord connection adapters and the PostgreSQL adapter to change the query string to replace “LIKE” with “ILIKE” before executing the query. However, this solution is the most confusing, and in the light of simpler ways, such as the upper part of both terms, I think it does not bother the effort (although you would get a lot of brown dots to do it this way).