How can I use % in a :conditions argument to ActiveRecord.find?

Question

I'm trying to do a query like this:

Widget.find(:all, :conditions => ["name like %awesome%"])

However, I'm getting a "malformed format string" exception from sanitize_sql, specifying the "%" as the problem.

How can I perform this query?

Answer 1

Try

Widget.find(:all, :conditions => ["name like '%awesome%'"])

Just added single quotes around the string %awesome%

Edit: Ok, that doesn;t actually work. The sql sanitizer is doing something screwy with the %s.

This will work.

Widget.find(:all, :conditions => ["name like ?","%awesome%"])

As per John Topley's answer you can make the string a variable if that is what you really need.

One tip I find useful when running into SQL errors is checking the development.log - that will list all the queries that are actually running against the database. Assuming you have basic knowledge of SQL it is often useful to debug them directly in a console, rather than making stabs at the ActiveRecord level (although I think in your case the code was barfing before it got to that stage)

Answer 2

Try this syntax:

term = "awesome"
Widget.all(:conditions => ["name LIKE ?", "%#{term}%"])

Answer 3

Your solution doesn't work because you don't have quotes around %awesome%.