File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Making the search in database case insensitive

 
shivangi mathur
Greenhorn
Posts: 10
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
I want to search the database for a keyword (that i am taking from a html page) with out taking the case in consideration. I am using JDBC-ODBC bridge to connect to database(ORACLE,SQL SERVER)
pls help.
Thanks in advance
Shivangi
 
Bosun Bello
Ranch Hand
Posts: 1510
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Convert both the keywor you are searching on and the corresponding ccolumn value in the Db to the same case before you do the comparison.
 
shivangi mathur
Greenhorn
Posts: 10
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for reply but the way you suggested is very costly in terms of performance as I hav to retrive each row from database for converting it into an uniform case.
can anyone suggest me another way??
is there any provision in SQL to do the same thing as I am using SELECT * FROM TABLE1 WHERE KEYWORDS LIKE '%keyword%'.
thanks in advance
shivangi
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
as soon as you introduce the keyword like, you are throwing your performance out the window anyways! You no longer use your indexes and do a full table scan. I don't think using a function like UPPER() will slow you down as much as you think! Try it, and see if you notice a difference.
Jamie
 
Jeff Price
Greenhorn
Posts: 2
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If your DB is read-intensive, and you are interested in one or very few columns to case-insensitive search, you could have a trigger and an extra column that automatically store a copy of the column in all upper (or lower) case, then search on that.
If you don't like that, try the following (as been previously suggested):
StringBuffer sql = new StringBuffer();
String thingToFind = "My Thing";
sql.append("SELECT columns FROM table WHERE ");
sql.append("UPPER(fieldToSearch) = '" + thingToFind.toUpperCase() + "'");
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic