Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Karthikeyan Bhaskaran
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34371
345
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Karthikeyan Bhaskaran
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34371
345
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 48910
58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Karthikeyan B. please read the important administrative private message I just sent you.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic