This week's book / license giveaways are in the JDBC and Relational Databases and Java in General forums.
We're giving away four copies each of PostGIS in Action and Java Advanced Topics Training and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and Relational Databases 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


JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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: 62488
    
  68

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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31798
    
192

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


[OCA 8 book] [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: 62488
    
  68

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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to do a query on a string type that is NOT case sensitive?