Menu
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.
965
Case Sensitivity Edit on GitHubString comparisons in PostgreSQL are case sensitive* (unless a case-insensitive collation were to be introduced).
While using regular expressions, we need to use the PostgreSQL ~* operator instead of the like operator; we can also use the ilike operator in PostgreSQL. We can also create an extension name as citext to use the case insensitive query in PostgreSQL; we need to create it first to use the extension of citext.
PostgreSQL names are case sensitive. By default, AWS Schema Conversion Tool (AWS SCT) uses object name in lowercase for PostgreSQL. In most cases, you'll want to use AWS Database Migration Service transformations to change schema, table, and column names to lower case.
The citext data type allows you to eliminate calls to lower in SQL queries, and allows a primary key to be case-insensitive. citext is locale-aware, just like text , which means that the matching of upper case and lower case characters is dependent on the rules of the database's LC_CTYPE setting.
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.
149
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
