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
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
Joined: Jul 07, 2001
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
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
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() + "'");