Case Sensitity with DB2

[SJW78]

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.

 

[papadba]

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?

 

[blom0344]

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

 

[Mercury2]

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

 

[LarrySteele]

Here's the method I use:

[tt]where upper({fieldname}) = 'CRITERIA'[/tt]

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

HTH,
Larry

 

[blom0344]

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

 

[LarrySteele]

Thanks Ties, I completely missed that!

 

[LarrySteele]

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