CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
legendary_rob
legendary_rob

Reputation: 13012

Ruby/Rails string case comparison issue's within postgres

I have a CSV that has a string column called name, It has things like "3 months pasta".

I want to map this to an existing DB table. The DB record has it saved as "3 months Pasta".

If I try Food.where(name: row['name']) it will come up empty. because it is doing a direct string to string comparison between "3 months pasta" and "3 months Pasta".

I have tried this as well

Food.where('name LIKE ?', '%' + "3 months pasta" + '%')

Which did not work unless I uppercase the "p", what can I do to get the strings to match on both ends? I want to do a row['name'].parameterize.underscore to get it to a single case and single style and then doing the same from the DB and matching them. The issue is doing this could be costly converting all the names then doing the comparison.

Is there something that I could do to achieve this?

Upvotes: 0

Views: 45

Answers (2)

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121020

Though the @Ursus’ answer is perfectly correct, I would go with native case insensitive search against DB:

Food.where('name = ? COLLATE utf8_general_ci', "3 months pasta")

ci in collation name stands for “case insensitive.”

Upvotes: 1

Ursus
Ursus

Reputation: 30071

Try

Food.where("lower(name) = ?", row['name'].downcase)

Upvotes: 2

Related Questions