aspose file tools*
The moose likes JDBC and the fly likes How to use underscore as literal not as wild char Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to use underscore as literal not as wild char" Watch "How to use underscore as literal not as wild char" New topic
Author

How to use underscore as literal not as wild char

cute nonni
Greenhorn

Joined: Jan 02, 2012
Posts: 7
Hello dears,


I'm using oracle and need to use the underscore as literal not as wild char in qurery

I found on the internet something like this

SELECT SAMPLE_NAME FROM SAMPLES WHERE SAMPLE_NAME LIKE 'ABC\_XYZ' ESCAPE '\'

and if there is SAMPLE_NAME equales ABC_XYZ it will be feached


but in my case the string that i compare to is variable(entred by user)

select SAMPLE_NAME FROM SAMPLES WHERE SAMPLE_NAME LIKE ?

Is there any way to disable using '_' as wildchar in general in queris???

or the only solution is to process user input before using it in LIKE statement !

thanks in advance
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29220
    
135

That is still the way to do it. You can use String's replaceAll method in Java to convert the string to have the escape character before submitting.

Your real example probably looks like "like % " + userInput + "%". If it doesn't have the % wildcard, you could just use "field = userInput" and be done with it.

Obviously, you still use a PreparedStatement here. I'm spelling out the input to make it clearer.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1106

I am sure i read about something in oracle to allow you to set your wildcards, in an old thread.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: How to use underscore as literal not as wild char
 
Similar Threads
using wildcards literally in queries
problem in generating query in MSAccess
selecting special characters
Get dialect from session (for character escaping)
Unexpected results from query....