INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Case Sensitity with DB2

Case Sensitity with DB2

(OP)
Hello,
When trying to filter for fields with upper & lower case values I am unable to match on all values due to the mix of the cases.  Does anyone have any suggestions on how to address this issue?  We are currently using upper case function however this is impacting our performace & results.
Any help would be greatly appreciated.

RE: Case Sensitity with DB2


Is the data stored in the table with mixed case?

If it is and the query presents the same mixed-case value, there should be no problem obtaining the matching results.

Possibly there is something i have misunderstood?

RE: Case Sensitity with DB2

The string 'EvEn' is not the same as 'eVeN'. If you need to evaluate 'EVEN' than there is no option other than using UPPER function AFAIK.
I Imagine that using the UPPER function invalidates the use of an index. Is it possible to index a calculated column where you store values in UPPER Case?

Ties Blom
 
 

RE: Case Sensitity with DB2

You could use the TRANSLATE(MyFld) function however I do not know if you'll get a performance hit.

RE: Case Sensitity with DB2

Here's the method I use:

where upper({fieldname}) = 'CRITERIA'

Likewise, you can use the lower() function to compare against lower cased text.

HTH,
Larry

RE: Case Sensitity with DB2

Larry,

The OP indicates he is using the upper function. The question is how to avoid loss of performance (probably due to non-index access)..

Ties Blom
 
 

RE: Case Sensitity with DB2

Thanks Ties, I completely missed that! surprise

RE: Case Sensitity with DB2

Having missed the mark on my first response, I'll offer this from IBM: Making DB2 Case-Insensitive.  If you're fortunate enough to have DB2 9.5, fixpack 1 adds the ability to make DB2 case insensitive.  (unable to tell if this was the first version to provide case sensitivity setting).

Other suggestions I've seen are to create a derived column that hold the upper cased text of the data column.  You'd then use triggers to update the derived column's value.  I'm not convinced I'd want to do this, but it is an option.

HTH,
Larry

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close