Rails MySQL ILIKE query

Why yes, because there's no such thing as ILIKE in MySQL. Only LIKE. You may have seen ILIKE as a case-insensitive version of LIKE in PostgreSQL, but your current RDBMS is different.

Your best bet is using LOWER on both sides to achieve mostly equivalent effect:

.where('LOWER(name) LIKE LOWER(?)', "%#{agency_name}%")

Credit to @mu is too short for his answer.

I think it should be:

 scope :by_name, lambda { |agency_name| 
   where('name LIKE ?', "%#{agency_name}%") # not ILIKE
 }

It is in PostgreSQL the keyword 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.

In MySQL you do not have ILIKE. Checkout MySQL docs on string comparison functions.

---

Bonus - you can also use Arel. Take a look:

scope :by_name, lambda { |agency_name| 
  where(Agency.arel_table[:name].matches("%#{agency_name}%"))
}