Strange behaviour when using LIKE with %

junk1

I am running a query from a Java app via the IBM type 4 driver back to
MF DB2 and am getting a strange result.

Basically my statement is

SELECT BLAH WHERE NAME LIKE ?

....and I pass in "Fr%" expecting it to match with "Fred" but it doesnt.

....but if I pass in "Fr%%" then it does match "Fred".

Several other combinations - "Sa%%y" matching "Sally" but "Sa%y" NOT
matching "Sally" seem to suggest that the '%' is being treated as a
single char wild card and not a 1 to many wildcard?

Am I going mad or should "Fr%" match "Fred"?
Thanks

David Bevan
http://www.davidbevan.co.uk

Nov 12 '05 #1

Jan M. Nelken

ju***@davidbevan.co.uk wrote:

Am I going mad or should "Fr%" match "Fred"?

While I may be tempted to answer first part of the question - it would be
sufficient to test from CLP that (using SAMPLE database):

select * from org where location like 'D%'

works fine (i.e delivers two qualifying rows. The same 2 qualifying rows are
delivered when running same query from JCC T4 driver.

So something is wrong with your code. Need more input.

Jan M. Nelken

Nov 12 '05 #2

junk1

I am trying to so a case insensetive select and so my where clause
looks like...

"where lcase(firstname) like lcase(cast(? as char(20)) )"

....but one of my colleages thinks this is dodgy SQL - plus it works if
I do...

"where lcase(firstname) like ?"

and do the toLowerCase() in Java.

So is the first where clause bad SQL?

David Bevan
http://www.davidbevan.co.uk

Nov 12 '05 #3

Brian Tkatch

If the SQL statement is using a variable, such as in a routine, and if
that variable is declared as CHAR, it will have this problem, because
all non-defined characters become spaces and must be matched.

and I pass in "Fr%" expecting it to match with "Fred" but it doesnt.
Because it is actually searching for "Fr% "
"Sa%%y" matching "Sally" but "Sa%y" NOT matching "Sally" seem to
suggest that the '%' is being treated as a single char wild card

Or that it is searching for one more trailing space.

To resolve, either declare the IN variable as VARCHAR, or replace all
spaces with wildcards REPLACE(Search, ' ', '%')

B.

Nov 12 '05 #4

Brian Tkatch

The "bad SQL" is anytime a WHERE clause does a single-row FUNCTION on a
COLUMN, because that negates the use of any INDEXes and forces a full
table scan.

I have a query where i needed to search some text excluding records
that had a caseless "x-" prefix in one of their fields. So, i used:

AND Text NOT LIKE 'X-%'
AND Text NOT LIKE 'x-%'

If you can figure out some other rules so the COLUMN can be used
without a FUNCTION, it would be "better SQL". Either that, or ADD a
COLUMN to the TABLE as a GENERATED ALWAYS AS LCASE(firstname). This
way, the original COLUMN is kept as is, and another COLUMN already has
it lowercased and would be availible for INDEXing.

As for where to lowercase the text, i doubt it matters, unless other
criteria is used for searching the first name. (This is something i
know from Oracle, i am not sure how DB2 does it.) Because, this will
affect how the query is stored. If the same query comes up a second
time, it would not need to be re-optimized if the text is exactly the
same. However, if you lcase in one query but not in the next, even
though the rest of the query is identical, the query will be
re-optimized. The time wasted is usually negligable, but can help to
bear in mind if these query are executed many times. So, it is good
practice to lowercase it in your java code instead.

B.

Nov 12 '05 #5

Ian

ju***@davidbevan.co.uk wrote:

I am running a query from a Java app via the IBM type 4 driver back to
MF DB2 and am getting a strange result.

Basically my statement is

SELECT BLAH WHERE NAME LIKE ?

...and I pass in "Fr%" expecting it to match with "Fred" but it doesnt.

...but if I pass in "Fr%%" then it does match "Fred".

Several other combinations - "Sa%%y" matching "Sally" but "Sa%y" NOT
matching "Sally" seem to suggest that the '%' is being treated as a
single char wild card and not a 1 to many wildcard?

Am I going mad or should "Fr%" match "Fred"?

Not sure about this, but I'd guess that using parameter markers and
SQL wildcards has different symantecs than straight SQL.

That is, a single percent '%' is probably being interpreted as a
literal percent sign, but '%%' is being interpreted as the wildcard
(similar to how you embed a escape single quote in a value, as in
select id from table where word = 'don''t')

So 'Fr%' would match 'Fr%', not 'Fra', 'Fre', 'Frx' etc.

Nov 12 '05 #6

Brian Tkatch

That shouldn't be the case, because it is a LIKE, and therefore unless
he used the ESCAPE parameter, the two globbing operators should not be
treated as literals. DB2 follows that standard. <URL:
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000751.htm>

B.

Nov 12 '05 #7

Mark Yudkin

The first SQL clause with the cast demands that your input be padded with
spaces to exactly 20 characters. For your sample input 'Fr%' you actually
have 17 exact-match blanks ('Fr% ').' Your query is doing
exactly what you asked it to, but apparently that's not what you want.

<ju***@davidbevan.co.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I am trying to so a case insensetive select and so my where clause
looks like...

"where lcase(firstname) like lcase(cast(? as char(20)) )"

...but one of my colleages thinks this is dodgy SQL - plus it works if
I do...

"where lcase(firstname) like ?"

and do the toLowerCase() in Java.

So is the first where clause bad SQL?

David Bevan
http://www.davidbevan.co.uk

Nov 12 '05 #8