MySQL in dev, Postgres in prod - differences in "LIKE" query

Hi everyone,

I run MySQL in my dev environment, but Postgres in my production
environment (out of necessity). I’m having trouble finding a way to
write a query with a LIKE condition that is supported as case
insensitive in both databases.

Right now, I have this:

@query = “m” # for example
@people = Person.find(:all, :conditions => [“last_name LIKE ?”, @query +
“%”])

This works in MySQL, but in Postgres, it’s case sensitive. If I
change it to support the Postgres syntax, it doesn’t work in MySQL.
Is there a more Rails way to handle this?

Thanks!

Sean

Postgres uses “ilike” for insensetive “like” comparisons. Maybe mysql
uses it too.

Alternatively, maybe you can force the query string to lowercase in
ruby, and use something like “LOWER(last_name) LIKE ?”

On Wed, 2006-03-29 at 14:04 -0500, Sean H. wrote:

@people = Person.find(:all, :conditions => [“last_name LIKE ?”, @query + “%”])

This works in MySQL, but in Postgres, it’s case sensitive. If I
change it to support the Postgres syntax, it doesn’t work in MySQL.
Is there a more Rails way to handle this?


can you use @query.downcase instead of @query?

Don’t you need “%” + @query.downcase + “%” #percent symbol on both
sides?

Craig

That’ll work! I checked around more and I couldn’t find a Rails way to
do it.

Thank you!

Sean

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs