• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

case insensitive search

 
Ranch Hand
Posts: 476
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 476
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok, but this will only test for case such as 'BOB' and not 'Bob' or 'boB'. Or will it?
 
Alex Kravets
Ranch Hand
Posts: 476
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
oh, ok...never mind, I got it.
 
This will take every ounce of my mental strength! All for a tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic