• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
King-Chieh Wang
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64618
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
King-Chieh Wang
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
lowercase(name) must be a database specific function. The generic sql function is lower(name)
Oreilly has a full list.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64618
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It always helps to have your database reference handy as SQL dialects are notoriously database-specific.
 
King-Chieh Wang
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic