File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Making the search in database case insensitive Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Make it so: Java DB Connections & Transactions this week in the JDBC forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Making the search in database case insensitive" Watch "Making the search in database case insensitive" New topic

Making the search in database case insensitive

shivangi mathur

Joined: Jul 07, 2001
Posts: 10
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
Bosun Bello
Ranch Hand

Joined: Nov 06, 2000
Posts: 1510
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.

So much trouble in the world -- Bob Marley
shivangi mathur

Joined: Jul 07, 2001
Posts: 10
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
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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.
Jeff Price

Joined: Jan 31, 2002
Posts: 2
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() + "'");
I agree. Here's the link:
subject: Making the search in database case insensitive
It's not a secret anymore!