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

Rails
1

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

2

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 ?”

3

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

4

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

Thank you!

Sean