File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and the fly likes Prepared statements with % wildcards Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared statements with % wildcards" Watch "Prepared statements with % wildcards" New topic

Prepared statements with % wildcards

Joe McClelland

Joined: Sep 10, 2004
Posts: 3
I'm trying to do something a little different here, I'd like to use a prepared statement to implement searching for a user.

My SQL (which doesn't work) looks like:

"SELECT * from users where name like '%?%'"

(postgres 7.4 is the database)

I want to do partial matches here, but it seems to be interpreting the ? as part of the string literal.

As expected, if I do this instead then it works but I force the user to enter % manually which I don't want to do:

"SELECT * from users where name like ?"

I know, I could just use a regular statement, but I'd rather not fumble with string concatenation if at all possible. Then I'd have to check for special charaters such as ' in the input, etc. Too much hassle!

Thoughts anyone?
Ranch Hand

Joined: Jul 14, 2004
Posts: 305
hmmmm... I'm not sure what you are trying to do. Can you explain it a bit more?

I would expect "SELECT * from users where name like '%?%'" to return all rows with a "?" in the name column.

[ September 10, 2004: Message edited by: Ray Marsh ]
[ September 10, 2004: Message edited by: Ray Marsh ]
Joe McClelland

Joined: Sep 10, 2004
Posts: 3
Hi Ray,

Thanks for the reply.

You are exactly right, "SELECT * from users where name like '%?%'" returns all rows with ? in the name column. It technically works, but just not the way I had hoped.

What I'm trying to do is pad the value from ? with two percent signs on either side. In effect, I was hoping that ? would be replaced with whatever "statement.setString(1, <somevalue> " would set it to, and that the two percent signs would remain allowing me to do a partial match.

But I think ? cannot be used as a literal replacement in any part of the SQL, only as a variable for a conditional statement within a clause. For example, I know that "SELECT * from ? where . . . " doesn't work, you can't use ? to make a table name a variable. I'm thinking what I'm trying to do isn't possible using a prepared statement and that I'll have to use a regular one instead and simply build the string at runtime (yuck!).

Ranch Hand

Joined: Jul 14, 2004
Posts: 305
Can you show me your code that creates the prepared statement and that sets the statement?

I'm thinking it should work. Try adding a space between the % and the ?, like this ---> % ? % <---- although the space might stay and mess up the select.

PreparedStatement updateSales;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME like ?";
updateSales = con.prepareStatement(updateString);

updateSales.setInt(1, 50);
updateSales.setString(2, "Espresso");

If I have a chance I'll take a swipe at it. I've never tried to do exactly that before.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Pardon me for stating the obvious, but just tack a "%" character to the start and end of the string entered by the user, and use that as the parameter value to your "PreparedStatement", for example:
[NOTE: Uncompiled and untested.]

But I think ? cannot be used as a literal replacement in any part of the SQL

Correct, "?" in the SQL string passed to a "PreparedStatement" can only be used anywhere that a literal value can be used.

Good Luck,
[ September 10, 2004: Message edited by: Avi Abrami ]
Joe McClelland

Joined: Sep 10, 2004
Posts: 3
Wow, I must be more tired than I thought. I had a "visitor" last night and she stayed quite late. Anyway, I guess I was trying too hard to figure out how to accomplish it in SQL, trying also to determine the nature and capabilities of wildcards in prepared statements. That is definitely the obvious solution that I will use.

Thanks Avi and Ray for your responses,

Ranch Hand

Joined: Jul 14, 2004
Posts: 305
Avi nailed it. That's the most obvious answer. However there is no forgiveness for stating the obvious, you must always answer for it!

I can use Joe's excuse too, except it was not for the same reason, I got a support call from work @ 12:45 and got back to bed @ 1:30. So, I'm sleepy too and have no business trying to say anything remotely intelligent.

Glad you got it solved.
wilson alwaris

Joined: Mar 14, 2011
Posts: 1
wow! Avi.. that was such a simple effective answer thanks a lot... i am using it for my college project after 7 year :-)
I agree. Here's the link:
subject: Prepared statements with % wildcards
Similar Threads
String vs MyStringBuffer.toString()
Prepared Statement with wild card
How to use Prepared statements when searching an unknown number of values
using prepare statements
use a Statement rather than PreparedStatement