CODEWITHSUNDEEP
ruby-on-railspostgresqlheroku
Christoffer
Christoffer

Reputation: 2411

Heroku/postgresql search

I have a database with names of cities that uses all different kinds of characters, from åäö to vietnamese signs. The place names are all in a capitalized format e.g. "New York" and "Aix En Provence".

My problem now is to try to search for these city names in Heroku which uses postgresql. 

p = Place.where("upper(name) LIKE '%" + place_name_searched.to_s.upcase.tr('åäöüñï','ÅÄÖÜÑÏ') + "%'").first

This is the best I have come up with but it is only but a fix for åäö etc and doesn't catch all the 100+ different characters there is out there, e.g. "é". The solution is not to fill out that string any further.

A general problem is that upper(name) seems to translate everything fine but upcase can only handle english letters. So the correspoding search will be .where("TÄBY like 'TäBY') if place_name_searched = 'täby'.

So, how can I match postgresql entries such as "Täby" and "Jidd Ḩafş" to corresponding strings in downcase, entered by the user ("täby", "jidd hafs")?

It all works fine in my Mysql-version of the application but once I upload to Heroku it all fails.

Upvotes: 1

Views: 349

Answers (1)

Yosep Kim
Yosep Kim

Reputation: 2951

Postgres has an extension called "ILIKE" for case insensitive pattern matching.

p = Place.where("upper(name) ILIKE '%" + place_name_searched.to_s.upcase.tr('åäöüñï','ÅÄÖÜÑÏ') + "%'").first

But please note this only works with Postgres, so you need to check which environment you are running on to decide if you would use this one instead of the regular one.

Upvotes: 2

Related Questions