Joe McClelland

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,

Joe

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!).

Joe.

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?