wood burning stoves 2.0*
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 Murach's Java Servlets and JSP this week in the Servlets 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() + "'");
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Making the search in database case insensitive
 
Similar Threads
Date field is giving problem...Urgent
Upper-case required in Where Clause?
Help regarding search API
Runtime intermediate storage - Design Problem
Sql query to match Strings?