• 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

DB2 - case sensitive or not

 
Ranch Hand
Posts: 175
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
When I do a search in a DB2 database table using a SQL string, for example, select * from table1 where column1 like 'Abc'.
Does the select condition in the where clause (e.g., 'Abc' in the above example) case sensitive or not? If is case sensitive, then how can I select everything from the table containing abc regardless the case of the characters?
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
to avoid case sensitivity you can use a DB2 procedure in your query (example is using Oracle) like:
"select * from table where UPPER( column1 ) like UPPER( '" + string_val + "') ");
*note: UPPER is the Oracle procedure for upper case conversion, not sure what the equivalent DB2 procedure is
You can find out if the column is case sensitive by doing some test queries.
Jamie
 
Ranch Hand
Posts: 1514
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You can follow Jamie's example and use UCASE instead of UPPER. UCASE is the DB2 equivalent of that function.
[ August 07, 2002: Message edited by: Bosun Bello ]
 
Mike Yu
Ranch Hand
Posts: 175
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
HI Jamie and Bosun,
Thank you for your help. One further question:
When using select * from table1 where column1 = 'abc', the select is NOT case sensitive. Is it right?
 
Bosun Bello
Ranch Hand
Posts: 1514
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Mike, it is not case sensitive. Why don't you try it and see what happens?
 
Mike Yu
Ranch Hand
Posts: 175
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Bosun,
Thank you again. I really tried. I just wanted to make sure that it is 100% right.
 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You only have to be concerned about the DATA regarding case.
For reserved words and metadata it doesn't matter ('cause DB2 is going to treat those as uppercase anyways).
(Just throwing in my two cents worth)
 
reply
    Bookmark Topic Watch Topic
  • New Topic