This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes case insensitive search Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "case insensitive search" Watch "case insensitive search" New topic
Author

case insensitive search

Alex Kravets
Ranch Hand

Joined: Jan 24, 2001
Posts: 476
Hello,
How do I construct an SQL query that is case insensitive? I want to be able to use strings such as 'BOB' or 'bob', or 'Dave' or 'dave'. Another words if user enters 'bob', in the database it could be listed as 'BOB' or Bob' or 'bob' or 'bOb' or 'boB'... you get the picture,. So how do I find by just entering 'bob' all 5 cases in this exampe?
thanks,
Alex


All right brain, you don't like me and I don't like you, but let's just do this one thing so I can get back to killing you with beer.<br /> <br />- Homer Simpson
Michael Zalewski
Ranch Hand

Joined: Jun 10, 2002
Posts: 30
Depends on the database you are using.
Look up string functions to convert to upper case, and use that. I think in Oracle, it's TO_UPPER. So you would convert your search term to upper case, then execute
SELECT * FROM Person
WHERE TO_UPPER( first_name) = 'BOB'
Of course, a better way to do this is to use a PreparedStatement.
PreparedStatement psFindBob = getConnection().prepareStatement(
"SELECT * FROM Person"
+ " WHERE TO_UPPER( first_name) = ?
);
psFindBob.setString( sBob.toUpperCase());
ResultSet rsFindBob = psFindBob.executeQuery();
...
PreparedStatement will perform better if you do this same query several times. It will also handle searching for names like O'Leary (embedded quotes or SQL reserved words).
Alex Kravets
Ranch Hand

Joined: Jan 24, 2001
Posts: 476
Ok, but this will only test for case such as 'BOB' and not 'Bob' or 'boB'. Or will it?
Alex Kravets
Ranch Hand

Joined: Jan 24, 2001
Posts: 476
oh, ok...never mind, I got it.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: case insensitive search
 
Similar Threads
How to compare each individual char of 2 string
Case Insensitve Search
Re: case-sensitivity foe username
is the search case sensitive?
case-iinsensitive