CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Jeremy
Jeremy

Reputation: 1002

How to perform a case insensitive search when lower(field) on the database is not the same as field.downcase in ruby?

I have the following simple active record query to search accross database for other people sharing the same company name (case insensitive)

In short:

company = "someTHING"
leads.where("lower(company) = ?", company.downcase)

Works well no matter the case of the company name.

The problem appeared when one user entered special character for the company

Échos

leads.where("company = ?", company) -> 2 results
leads.where("lower(company) = ?", company.downcase) -> 0 result

with "à L'Express", it works in both cases

This means that database lower("É") is not equal to É.downcase

>> company
=> "Échos"
>> company.downcase
=> "Échos"

How can I perform a search that is case insensitive AND can find the result in this case?

Upvotes: 1

Views: 54

Answers (2)

sockmonk
sockmonk

Reputation: 4255

Using the database's lower is probably the best answer, as glebm said. Other options might be to use ILIKE or postgresql's regexp features, but those options would probably be less able to use any index on the field and therefore be less performant.

Upvotes: 0

glebm
glebm

Reputation: 21130

Use the database lower:

where('lower(company) = lower(?)', company)

Upvotes: 4

Related Questions