This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC 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 The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Making the search in database case insensitive" Watch "Making the search in database case insensitive" New topic
Author

Making the search in database case insensitive

shivangi mathur
Greenhorn

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

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.


Bosun (SCJP, SCWCD)
So much trouble in the world -- Bob Marley
shivangi mathur
Greenhorn

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
shivangi
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.
Jamie
Jeff Price
Greenhorn

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: http://aspose.com/file-tools
 
subject: Making the search in database case insensitive