This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes using wildcards literally in queries 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 "using wildcards literally in queries" Watch "using wildcards literally in queries" New topic

using wildcards literally in queries

Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
I want to retrieve rows from an Oracle table which has values like "sp_bowl" or "asp_007" etc. My query is like
I'm using PreparedStatements, and passing Strings using preparedStatement.setString(1,"%"+myString+"%").
When the use enters "sp_" as the string to be passed to the query, I'm getting all rows which have "sp" in them, but not ones which have the UNDERSCORE character in them. How can I force the Underscore character to be treated literally and not as a wildcard ?
Any suggestions would be welcome.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

insert the Oracle escape character \ in your query:
select * from table where column1 like '%sp\_bowl'
this would find all values that end in "sp_bowl" literally.

Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
Thanks Jamie. It worked :-)
I agree. Here's the link:
subject: using wildcards literally in queries
It's not a secret anymore!