Rails - Mar 2006 - 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 3/29/06, Sean Hussey <seanhussey@gmail.com>
wrote:
> 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
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On Wed, 2006-03-29 at 14:04 -0500, Sean Hussey wrote:
> 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?
----
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

On 3/29/06, Ian Harding <iharding@destinydata.com>
wrote:
> 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 3/29/06, Sean Hussey <seanhussey@gmail.com> wrote:
> > 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
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>