# Issue with SQL like

**URL:** <https://www.ruby-forum.com/t/issue-with-sql-like/132239>\
**Category:** Ruby\
**Created:** [March 18, 2008, 7:21am UTC](https://www.ruby-forum.com/t/issue-with-sql-like/132239 "2008-03-18T07:21:50Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![rajatgarg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/a183cd/32.png) [@rajatgarg](https://www.ruby-forum.com/u/rajatgarg)\
**Post date:** [March 18, 2008, 7:21am UTC](https://www.ruby-forum.com/t/issue-with-sql-like/132239/1 "2008-03-18T07:21:50Z")

</div>

Hi Guys,

I am a newbie, so excuse me if this is a lame question -

I am writing a query -  
Airport.find(:all, :conditions =\>[“owner\_city\_state\_zip like ‘%?%’”,  
\_tempZip.zip])

Now, this gives an error -

Mysql::Error: You have an error in your SQL syntax; check the manual  
that corresponds to your MySQL server version for the right syntax to  
use near ‘97103’%‘)’ at line 1: SELECT \* FROM airports WHERE  
(owner\_city\_state\_zip like ‘%‘97103’%’)

How do I format my query to get rid of this problem?

Also, how do we write a query to written say, 2 fields out of table  
instead  
of all the fields.

Thx,

–  
Rajat G.

Ph: 206-499-9495  
Add: 1314 Spring Street, #412  
Seattle, WA 98104  
Web: [http://www.pilotoutlook.com](http://www.pilotoutlook.com)

---

<div class="post-metadata">

**Author:** ![rajatgarg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/a183cd/32.png) [@rajatgarg](https://www.ruby-forum.com/u/rajatgarg)\
**Post date:** [March 18, 2008, 11:17am UTC](https://www.ruby-forum.com/t/issue-with-sql-like/132239/2 "2008-03-18T11:17:59Z")

</div>

On 18 Mar 2008, at 06:21, Rajat G. wrote:

> I am writing a query -  
> Airport.find(:all, :conditions =\>[“owner\_city\_state\_zip like ‘%?%’”,  
> \_tempZip.zip])  
> Assuming that you are using Rails, you need to do something like this:

Airport.find(:all, :conditions =\> [“owner\_city\_state\_zip LIKE ?”,  
“%#{\_tempZip.zip}%”])

> Also, how do we write a query to written say, 2 fields out of table  
> instead  
> of all the fields.  
> You need to use the `select` option as detailed in the API:  
> [http://www.railsbrain.com/api/rails-2.0.2/doc/index.html?a=M001686&name=find](http://www.railsbrain.com/api/rails-2.0.2/doc/index.html?a=M001686&name=find)

For example, if you only wanted to fetch the `id` and `name` fields of  
your airport record:

Airport.find(:all, :select =\> ‘id, name’, :conditions =\>  
[“owner\_city\_state\_zip like ?”, “%#{\_tempZip.zip}%”])

Note that you will still get a full Airport instance but only those  
fields in the select will have been loaded.

– Paul

---

<div class="post-metadata">

**Author:** ![rajatgarg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/a183cd/32.png) [@rajatgarg](https://www.ruby-forum.com/u/rajatgarg)\
**Post date:** [March 18, 2008, 4:42pm UTC](https://www.ruby-forum.com/t/issue-with-sql-like/132239/3 "2008-03-18T16:42:10Z")

</div>

This is great. Thanks, Paul

On Tue, Mar 18, 2008 at 3:16 AM, Paul M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > Also, how do we write a query to written say, 2 fields out of table
> 
> Note that you will still get a full Airport instance but only those  
> fields in the select will have been loaded.
> 
> – Paul

–  
Rajat G.

Ph: 206-499-9495  
Add: 1314 Spring Street, #412  
Seattle, WA 98104  
Web: [http://www.pilotoutlook.com](http://www.pilotoutlook.com)

---

<div class="post-metadata">

**Author:** ![rajatgarg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/a183cd/32.png) [@rajatgarg](https://www.ruby-forum.com/u/rajatgarg)\
**Post date:** [March 19, 2008, 6:13pm UTC](https://www.ruby-forum.com/t/issue-with-sql-like/132239/4 "2008-03-19T18:13:44Z")

</div>

On Tue, 18 Mar 2008 01:21:02 -0500, Rajat G. wrote:

> Now, this gives an error -
> 
> Mysql::Error: You have an error in your SQL syntax; check the manual  
> that corresponds to your MySQL server version for the right syntax to  
> use near ‘97103’%’)’ at line 1: SELECT \* FROM airports WHERE  
> (owner\_city\_state\_zip like ‘%‘97103’%’)

Yuck. That doesn’t look like the right behavior. I would think that the  
quoted question mark should be passed as-is to the database, and not  
substituted for a properly escaped string value. (So that the actual  
pattern matched is ‘%?%’)

Any idea whether this can be fixed?

---

<div class="post-metadata">

**Author:** ![rajatgarg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/a183cd/32.png) [@rajatgarg](https://www.ruby-forum.com/u/rajatgarg)\
**Post date:** [March 19, 2008, 6:46pm UTC](https://www.ruby-forum.com/t/issue-with-sql-like/132239/5 "2008-03-19T18:46:12Z")

</div>

On Mar 19, 2008, at 1:10 PM, Ken B. wrote:

> > 
> 
> Any idea whether this can be fixed?
> 
> –  
> Ken (Chanoch) Bloom. PhD candidate. Linguistic Cognition Laboratory.  
> Department of Computer Science. Illinois Institute of Technology.  
> [http://www.iit.edu/~kbloom1/](http://www.iit.edu/~kbloom1/)

You should write this as:

Airport.find(:all, :conditions =\>[“owner\_city\_state\_zip like ?”,  
“%#{\_tempZip.zip}%”])  
Or:

Airport.find(:all, :conditions =\>[“owner\_city\_state\_zip like ?”,  
“%” + \_tempZip.zip + “%”])

Each ? will be replaced with the value later in the array quoted as  
appropriate for its type.

-Rob

Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid