aspose file tools*
The moose likes JDBC and the fly likes How to do a SELECT WHERE a LIKE b when b has a '%'? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to do a SELECT WHERE a LIKE b when b has a Watch "How to do a SELECT WHERE a LIKE b when b has a New topic
Author

How to do a SELECT WHERE a LIKE b when b has a '%'?

Yuriy Zilbergleyt
Ranch Hand

Joined: Dec 13, 2004
Posts: 429
Hello,

Say you are looking for a record where a varchar column contains a value like '15%'. Obviously using "WHERE colName LIKE '%15%%'" is not correct. What is the proper syntax for this?

Thank you,
Yuriy
[ October 14, 2005: Message edited by: Yuriy Zilbergleyt ]
Padma Lalwani
Ranch Hand

Joined: Nov 02, 2004
Posts: 49
In SQL Server it would be

like '%15[%]%', enclosing a wildcard in [] makes it a literal

Padma
Yuriy Zilbergleyt
Ranch Hand

Joined: Dec 13, 2004
Posts: 429
But there's no generic SQL or JDBC way of handling this?

Thanks,
Yuriy
Padma Lalwani
Ranch Hand

Joined: Nov 02, 2004
Posts: 49
JDBC specification is:

WHERE colName like '%15\\%%' {escape '\\'}

Here you use backslash as escape character for %, and then specify explicitly that backslash has been used as escape character and not a literal with the following clause
{escape '\\'}


In SQL it would be

WHERE colName like '%15\%%' {escape '\'}


Padma
Yuriy Zilbergleyt
Ranch Hand

Joined: Dec 13, 2004
Posts: 429
I just tried running the query

select * from testtable where name like '%15\%%' {escape '\'};

through Oracle/sqlplus, and it gave an "invalid character" error for the '{'.
Does that mean JDBC will parse the SQL and used the vendor-specific way to escape? I thought that was only done through setXXX methods of PreparedStatements?

Thank you,
Yuriy
Sunil Dumpala
Greenhorn

Joined: Jul 28, 2005
Posts: 29
Yuriy,

Remove the line "{escape '\'}" and try just the following statement.

select * from testtable where name like '%15\%%' ;

I believe Padma was trying to explain that backslash is an escape character and she included it in paranthesis.

Thanks,
Sunil
Yuriy Zilbergleyt
Ranch Hand

Joined: Dec 13, 2004
Posts: 429
Thanks for the reply Sunil!

After looking it up, it turned out that Padma's answer is really the standard way JDBC uses escapes. The SQL standard way for LIKE escapes turned out to actually be

select * from testtable where name like '%15\%%' escape '\'

However this only works for the %'s and _'s and what do you do if you're matching against something that already has a backslash?

The simple

select * from testtable where name like '%15\%%'

only works for databases that use the backslash as an escape character. This isn't standard SQL. MySQL, the database I'm actually using for this project, does this. Oracle does not. I'm going to go with this method for now, but that means the code isn't very portable. And there's still the difficulty in searching for something containing a slash...

Thanks,
Yuriy
Padma Lalwani
Ranch Hand

Joined: Nov 02, 2004
Posts: 49
That is the exact reason we specify what we are using as ecape character! Because escape character is not and need not be standard

If we assume that \ is always used as escape, we wouldnt need the clause { escape '\' }


For example if you had two values

'15\aa' and '15%aa' in your table

select * from test where name like '%15\%%' escape '\'
This would escape '%' using '\'

select * from test where name like '%15$\%' escape '$'
This would escape '\' using '$'

The escape clause is in there to enable you to use any escape character of your choice, not just '\'

Padma
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: How to do a SELECT WHERE a LIKE b when b has a '%'?