This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Soft Skills: The software developer's life manual and have John Sonmez on-line!
See this thread for details.
Win a copy of Soft Skills: The software developer's life manual this week in the Jobs Discussion forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Yuriy Zilbergleyt
Ranch Hand
Posts: 429
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 49
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In SQL Server it would be

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

Padma
 
Yuriy Zilbergleyt
Ranch Hand
Posts: 429
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But there's no generic SQL or JDBC way of handling this?

Thanks,
Yuriy
 
Padma Lalwani
Ranch Hand
Posts: 49
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 429
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 29
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 429
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 49
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic