File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Retrieve results for both 'abc' and 'ABC': SELECT statement and WHERE clause has 'abc' Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Retrieve results for both Watch "Retrieve results for both New topic
Author

Retrieve results for both 'abc' and 'ABC': SELECT statement and WHERE clause has 'abc'

Karthikeyan Bhaskaran
Greenhorn

Joined: Jul 02, 2007
Posts: 5
Hi everybody,

Maybe I'm posting (I've posted) this in the wrong forum. Apologies.

In a JSP with a Search field, would it be possible to retrieve results for case insensitive combinations of 'abc' from a JSP-level modification?

For example, if I enter "doyle" in the Search box to invoke a "select * from BOOKS where AUTHOR='doyle'" through a servlet, is it possible to retrieve results for Doyle, doyle, DOYLE etc, through some modification at the JSP level?

Thanks in advance.

Karthikeyan.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29261
    
140

Karthikeyan,
This is the right forum.

Tip #1 - get the JDBC code out of the JSP. It's poor practice to mix the database and presentation layers in addition to being harder to maintain.
Tip #2 - Use a prepared statement so SQL injection can't happen. (Google it for why this is a bad thing)

On to your question, the answer is yes.
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
Karthikeyan Bhaskaran
Greenhorn

Joined: Jul 02, 2007
Posts: 5
Thanks for the responses.

The JSP has only the Search field - no SQL. A button-click invokes a servlet which retrieves the results from DB. My search retrieves only case-sensitive results for 'doyle'. I am trying to see if at all there is any possibility of pushing the database to give me case-insensitive results by making only changes to the JSP such as generate all possible cases for an input String and then retrieve all records for doyle, Doyle, etc.
But, I guess that will corrupt the JSP into doing more than what JSP is supposed to do.

Thank you for your time.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29261
    
140

Karthikeyan B. wrote:A button-click invokes a servlet which retrieves the results from DB. My search retrieves only case-sensitive results for 'doyle'.

Oh good! Now that's proper design.

Sorry you do have to change the SQL, not just the JSP.
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
select * from BOOKS where to_upper(AUTHOR)=to_upper('DOYLE')


Revisiting this problem: Assuming there's an index on author, it probably won't be used would it...?
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2477
    
    7

John Kimball wrote:
Revisiting this problem: Assuming there's an index on author, it probably won't be used would it...?
No, most would stop using the index. But some databases allow you to create an index on a function. If you have such a database, you can create an index on to_upper(AUTHOR).
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
Ooh. Thanks for that. Did a quick check and noticed it's newly available in my favorite DBMS (Sybase).

I really need to look up a checklist of features to see what's been added into Sybase 15
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 36514
    
  16
Karthikeyan B. please read the important administrative private message I just sent you.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Retrieve results for both 'abc' and 'ABC': SELECT statement and WHERE clause has 'abc'
 
Similar Threads
Retain search results
Struts 1.2 - Tiles - Wrong Action Being Submitted
retain search and sorting results
How to retain search results
DB2 - case sensitive or not