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 How to do a query on a string type that is NOT case sensitive? 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 "How to do a query on a string type that is NOT case sensitive?" Watch "How to do a query on a string type that is NOT case sensitive?" New topic
Author

How to do a query on a string type that is NOT case sensitive?

King-Chieh Wang
Greenhorn

Joined: Jan 26, 2004
Posts: 24
Hello All!
This may be a beginner question... I am trying to submit a query on DVD title (varchar2 type) and I want to ignore the case... How can I do that? For example: title = '%Matrix%' is same as title = '%matrix%' ...etc
Thanks,
Jack
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60798
    
  65

Lowercase the search criteria.
For example:

of course, be sure that your like clause is lowercase as well.
[ April 26, 2004: Message edited by: Bear Bibeault ]

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
King-Chieh Wang
Greenhorn

Joined: Jan 26, 2004
Posts: 24
Thanks!
What about in cases where user input search as '%Gone%', and I want to be able to return all results containing '%GONE%' and '%gOnE%' and... so forth... just basically the seach should be totally not case sensitive?
Jack
King-Chieh Wang
Greenhorn

Joined: Jan 26, 2004
Posts: 24
Humm...
Actually, I tried the above code "where lowercase(name) like 'serach_name'" and it gives me a SQL error: invalid column name...
I surely put the right name in...b/c it would work if I didnt use lowercase() Any idea whats wrong w/ my code?
Thanks
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30136
    
150

lowercase(name) must be a database specific function. The generic sql function is lower(name)
Oreilly has a full list.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60798
    
  65

It always helps to have your database reference handy as SQL dialects are notoriously database-specific.
King-Chieh Wang
Greenhorn

Joined: Jan 26, 2004
Posts: 24
Thanks!! It worked!!
Also, thanks for the tip...I suppose I really need a book teaching SQL... online tutorial are great except they dont have an 'reference page' or index...
Thanks again!!
Jack
 
Consider Paul's rocket mass heater.
 
subject: How to do a query on a string type that is NOT case sensitive?
 
Similar Threads
How do I handle "The Matrix" ?
GOTO in JSF?
Array Counters
Learning Project 2
Class Design Problem