db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Martin Kalén <mka...@apache.org>
Subject Re: Oracle and case-insensitive search
Date Tue, 24 Aug 2004 11:41:30 GMT
Frank Renaers wrote:

> In Oracle a search on a string (Criteria.addLike) seems to be Case
> Sensitive.
> 
> How do you guys solve this problem.

Normally with Oracle, you would create your indexes on UPPER(COLUMN_NAME) and use
Java's String.toUpperCase() function when performing like-searches.

> In MsSql and MySql a Criteria.addLike is not case sensitive !

Then change your code to use .toUpperCase and your criteria to user Uit will work 
cross-platform between all three of them with your current OJB version.


I don't think this will be added permanently to OJB since we then loose the possibility to

distinguish between eg "SELECT x FROM y WHERE z LIKE 'value%';" and "SELECT x FROM y WHERE

z LIKE 'VALUE%'" in able databases like Oracle. (Plus we can't really break existing 
semantics.)

What we could consider is to use the PostreSQL middle way with eg "Criteria.addIlike". 
PostgreSQL uses the ILIKE-operator for case-insensitive (locale-aware) matching.

Hibernate's Expression works like this. See 
http://www.hibernate.org/hib_docs/api/net/sf/hibernate/expression/Expression.html


MySQL seems to be able to perform case-sensitive matching with a 'binary string' 
directive. See http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html


MSSQL also seems to be able to get case-sensivity either at install-time or by 
specififying collation parameters at query-time. See 
http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm


Keep existing RDBMS-depentent functionality and add two new well-definied methods 
controlled by the platform impl?

Any comments? Jakob?

Regards,
  Martin

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message