Rails MySQL ILIKE query
Solution 1
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}%"))
}
Solution 2
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.
Neil
Updated on July 22, 2022Comments
-
Neil almost 2 years
My syntax is off. I want to create a scope
by_name
that finds allagencies
whose name attribute contains the passed in string (case insensitive).Here is what I have:
class Agency < ActiveRecord::Base scope :by_name, ->(agency_name) { where('name ILIKE ?', "%#{agency_name}%") } end
In the rails console I type in
agencies = Agency.by_name("foo")
. Here is the query generated:SELECT `agencies`.* FROM `agencies` WHERE (name ILIKE '%foo%')
Here is the error message:
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ILIKE '%foo%')
-
Gary Benade almost 6 yearsThis is not an ideal solution because this will not allow the query optimiser to use an index if one exists on the name column, a better solution would be to set the collation of your table to utf8_general_ci - the ci means case insensitive, doing this will make all text matches case insensitive
-
Vael Victus almost 5 years@GaryBenade What if I want to allow the user to search with a case-sensitivity flag on or off?
-
D-side almost 5 years@VaelVictus that would be, admittedly, stretching MySQL's purpose, but for lack of functional indexes, both versions (wrt. case insensitivity) can be stored and indexed instead. Or a separate search server like ElasticSearch could be used for this purpose instead.