Like operand and case sensitivity

[erkki]

Hi,

is there some DB parameter or JDBC driver property that says 'LIKE'
search should not be case sensitive?

I use DB2 Express 8.1.5 and Websphere 5.0.2. I don't use WAS
Connectionpool in this case.

UCASE(fieldname) is not an option.

[Knut Stolze]

erkki wrote:

Hi,

is there some DB parameter or JDBC driver property that says 'LIKE'
search should not be case sensitive?

I use DB2 Express 8.1.5 and Websphere 5.0.2. I don't use WAS
Connectionpool in this case.

UCASE(fieldname) is not an option.

Why is this not an option?

You could:
(a) insert only upper case values in your tables
(b) use your own functions as described here
http://www.ibm.com/developerworks/db...301stolze.html

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[erkki]

Hi,

I just wanted to be sure that there is no similiar way in DB2Express
like there is in iSeries. In iSeries you can do that with driver properties.

I have done this think with UCASE and it works fine. And after searching
all around www I am quite sure now that DB2 can not do like search case
insensitive way...

The problem is that my application runs on iSeries or DB2 database, and
that iSeries does some extra work now with that UCASE function. It
really don't need to do so.

Well of course I could write my code that way it checks whether database
reside on iSeries and if so sql can be executed without UCASE()...

Knut Stolze wrote:

erkki wrote:

Hi,

is there some DB parameter or JDBC driver property that says 'LIKE'
search should not be case sensitive?

I use DB2 Express 8.1.5 and Websphere 5.0.2. I don't use WAS
Connectionpool in this case.

UCASE(fieldname) is not an option.

Why is this not an option?

You could:
(a) insert only upper case values in your tables
(b) use your own functions as described here
http://www.ibm.com/developerworks/db...301stolze.html

[Blair Adamache]

We had a fix in DB2 v7 to address this - you'd need to contact IBM
Service to get this forward fit into v8 if it met your needs:

Defect number - IY13707

NEW PATCH2=41 IN CLI TO HELP USERS DO CASE INSENSITIVE QUERIES:
VIEWS HAS TO BE CREATED ON A TABLE TO UPPER CASE EVERYTHING, THE

ERROR DESCRIPTION:
new PATCH2=41 in CLI to help users do case insensitive queries:
views has to be created on a table to Upper case everything,
then with above PATCH cli will convert all literals upper case
too.

erkki wrote:

Hi,

is there some DB parameter or JDBC driver property that says 'LIKE'
search should not be case sensitive?

I use DB2 Express 8.1.5 and Websphere 5.0.2. I don't use WAS
Connectionpool in this case.

UCASE(fieldname) is not an option.

[Blair Adamache]

You can aslo use generated columns that store each value in upper case.

erkki wrote:

Hi,

I just wanted to be sure that there is no similiar way in DB2Express
like there is in iSeries. In iSeries you can do that with driver
properties.

I have done this think with UCASE and it works fine. And after searching
all around www I am quite sure now that DB2 can not do like search case
insensitive way...

The problem is that my application runs on iSeries or DB2 database, and
that iSeries does some extra work now with that UCASE function. It
really don't need to do so.

Well of course I could write my code that way it checks whether database
reside on iSeries and if so sql can be executed without UCASE()...

Knut Stolze wrote:

erkki wrote:

Hi,

is there some DB parameter or JDBC driver property that says 'LIKE'
search should not be case sensitive?

I use DB2 Express 8.1.5 and Websphere 5.0.2. I don't use WAS
Connectionpool in this case.

UCASE(fieldname) is not an option.

Why is this not an option?

You could:
(a) insert only upper case values in your tables
(b) use your own functions as described here
http://www.ibm.com/developerworks/db...301stolze.html