March 20, 2008 at 5:53 am
Hi,
I am having trouble with a SQL query I am using via JDBC, essentially the problem boils down to the two below queries behaving differently:
select *
from table
where field1 in ('blah', '%')
select *
from table
where field1 like '%'
the 2nd query returns all records however the 1st only returns records with field1 matching 'blah'. Shouldn't these work the same in? the documentation I have looked at doesn't seem to mention wildcards behaving differently as part of 'in' statements?
Thanks.
March 20, 2008 at 6:15 am
The IN operator with a list is a short hand for a series of OR statements with =.
using a wildcard with an IN operator is like specifying a where COL1 = '%'.
Jez
March 20, 2008 at 7:05 am
ah ok, that makes sense then, thanks for the response.
In that case is there any parameter that can be supplied to a 'field1 =" clause so that it returns all results? Can't really change the statement due to the other things it is used for so if not I will have to write another one specifically for this.
March 20, 2008 at 7:20 am
Jonathan Devine (3/20/2008)
ah ok, that makes sense then, thanks for the response.In that case is there any parameter that can be supplied to a 'field1 =" clause so that it returns all results? Can't really change the statement due to the other things it is used for so if not I will have to write another one specifically for this.
Use the like clause. If you don't supply a wildcard then the like clause behaves the same as the equals sign.
select * from table where field1 like '%' -- returns all rows
select * from table where field1 like 'abc' -- is the same as select * from table where field1 = 'abc'
March 20, 2008 at 7:25 am
Thanks, unfortunately I can't use the like clause because essentially what I am trying to do is supply a parameter to the below clause that causes all rows to be returned:
select *
from table
where field1 in (? , ?)
The query is called from Java code that fills in the ? symbols, as per my previous post I can't change the query (without making the code a horrific mess) due to other parts of the system that use it, so I guess I'm just gonna have to write another one, which is what I was hoping to avoid.
I appreciate the replies, thanks for your help.
March 20, 2008 at 8:24 am
IN that case, maybe you should think about a FUL TEXT index. With that you can use the CONTAINS clause:
WHERE CONTAINS ( field1, 'blah' or '%' )
DAB
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply