Rails PostgreSQL non-case sensitive search with LIKE

Question

I have this in my controller:

Konkurrencer.where("title LIKE ?", "%#{params[:q]}%").limit(4)

I think this query is case sensitive. It should not be case sensitive.

Answer 1

You can use ILIKE in the where instead:

Konkurrencer.where("title ILIKE ?", "%#{params[:q]}%").limit(4)

From doc:

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

Answer 2

For using case insensitive in search with PostgreSQL using LOWER...

Example:

def self.search(client, date_start, date_end)
       joins(:customer).where("LOWER(customers.name) LIKE ? AND date >= ? AND date <= ?", "%#{client}%", date_start, date_end)
end