File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Unexpected results from query.... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Unexpected results from query...." Watch "Unexpected results from query...." New topic

Unexpected results from query....

Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
I'm executing a query which is like:
I'm using PreparedSatements for this, and passing the value entered by the user. If user wants to search for a name beginning with Ma_, I'm passing 'Ma_%' to the SELECT statement. But I get all rows beginning with Ma, including ones like Ma9189,Ma7594, then I get Ma_744, till Ma_zzz, and Maaaa till Mazzz. I expected to see only usernames beginning with Ma_. Why are all numbers beginning with Ma also shown, and also all user names beginning with Ma not having the underscore ?
Any clarifications will be very helpful.
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
the underscore is also a seach character meaning a single position. so 'MA_' returns MAA, MAB, ...
When you put MA_% you'll end up just like it was MA%
Andrew Shafer
Ranch Hand

Joined: Jan 19, 2001
Posts: 338

You should be able to use an escape character or something to get the search you wanted, the specifics might vary from Db to Db.
Also, if you have patterns that contain numbers in the username they will obviously be returned if they match your query.

Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
Thanks a lot Daniel and Andrew. Your answers were very helpful.
I agree. Here's the link:
subject: Unexpected results from query....
It's not a secret anymore!