Heroku/postgresql search

Question

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.

not directly related, but it is always good to remind it : beware of SQL injections — m_x
– m_x, Commented May 4, 2012 at 14:21
You may also be interested in the extension unaccent. Not sure if Heroku lets you install it (per database) with CREATE EXTENSION unaccent; PostgreSQL 9.0 or later. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 4, 2012 at 16:41

Yosep Kim · Accepted Answer · 2012-05-04 12:11:57Z

2

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.

answered May 4, 2012 at 12:11

Yosep Kim

2,96125 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Christoffer Over a year ago

That worked perfect, I had to use it with an if to check for Production or Development but that was a quickfix. Thanks!

Yosep Kim Over a year ago

Glad it worked for you. Although Rails offers you the awesome ORM capability, I think it is always good to have the same setup for different environments. I am still using sqlite on my dev box, but one day I will migrate it to postgres. One day! :D

Collectives™ on Stack Overflow

Heroku/postgresql search

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related