You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Frank Renaers <Fr...@ikan.be> on 2004/08/20 11:42:37 UTC
Oracle and case-insensitive search
Hi,
In Oracle a search on a string (Criteria.addLike) seems to be Case
Sensitive.
How do you guys solve this problem.
In MsSql and MySql a Criteria.addLike is not case sensitive !
Thanks,
Frank Renaers
Re: Oracle and case-insensitive search
Posted by Danilo Tommasina <dt...@risksys.com>.
Hi,
you can just do something like this...
Criteria c = new Criteria();
c.addLike( "upper(yourField)", yourString.toUpperCase() );
cheers
Danilo
> Hi,
>
>
>
> In Oracle a search on a string (Criteria.addLike) seems to be Case
> Sensitive.
>
> How do you guys solve this problem.
>
> In MsSql and MySql a Criteria.addLike is not case sensitive !
>
>
>
> Thanks,
>
>
>
> Frank Renaers
>
>
>
>
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle and case-insensitive search
Posted by "Bernhard v. Fromberg" <b....@virtual-solution.de>.
Hi,
try Criteria.addLike("Upper('foo')", bar.toUpperCase());
I think it could work and remember you can create an
index on UPPER(foo) in Oracle as far as I know.
Ciao,
Bernhard
> hi frank,
>
> ojb simply uses an sql LIKE, there is no magic added. so it's the dbms
> that treats the LIKE cas sensitive or insensitive.
>
> jakob
>
> Frank Renaers schrieb:
>
>> Hi,
>>
>>
>>
>> In Oracle a search on a string (Criteria.addLike) seems to be Case
>> Sensitive.
>>
>> How do you guys solve this problem.
>>
>> In MsSql and MySql a Criteria.addLike is not case sensitive !
>>
>>
>>
>> Thanks,
>>
>>
>>
>> Frank Renaers
>>
>>
>>
>>
>>
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
--
Mit freundlichen Gru"ssen,
Bernhard Fromberg
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle and case-insensitive search
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi frank,
ojb simply uses an sql LIKE, there is no magic added. so it's the dbms that
treats the LIKE cas sensitive or insensitive.
jakob
Frank Renaers schrieb:
> Hi,
>
>
>
> In Oracle a search on a string (Criteria.addLike) seems to be Case
> Sensitive.
>
> How do you guys solve this problem.
>
> In MsSql and MySql a Criteria.addLike is not case sensitive !
>
>
>
> Thanks,
>
>
>
> Frank Renaers
>
>
>
>
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Oracle and case-insensitive search
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi frank,
ojb simply uses an sql LIKE, there is no magic added. so it's the dbms that
treats the LIKE cas sensitive or insensitive.
jakob
Frank Renaers schrieb:
> Hi,
>
>
>
> In Oracle a search on a string (Criteria.addLike) seems to be Case
> Sensitive.
>
> How do you guys solve this problem.
>
> In MsSql and MySql a Criteria.addLike is not case sensitive !
>
>
>
> Thanks,
>
>
>
> Frank Renaers
>
>
>
>
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle and case-insensitive search
Posted by Martin Kalén <mk...@apache.org>.
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