Board index » delphi » Interbase: Case Sensitive ?

Interbase: Case Sensitive ?

Hello

A colleague of mine is using WNT Workstation, D2 and Interbase (local).
When executing a query that contains the following ' where customername
like %boe% ' it appears that the results are case sensitive - that is
'Boeing' will not be reutrned by the query whereas 'boeing' will be. We
need it to be NOT case sensitive...

We pored over all the documentation we have, to no avail. We suspect
that there is an Interbase parameter that drives this behaviour. Has
anyone senn (and solved) this?

Any help appreciated.

Regards,

Jean-Rene Nicolet
for Clinton Langan

 

Re:Interbase: Case Sensitive ?


Most SQL servers are case-sensitive unlike desktop databases.  There are
a number of workarounds.  In Oracle you can use the UPPER function as in
   where upper (customername) like '%BOE%'
I don't believe this works in Interbase.  The solution for it that I've
heard is to create a column that is the upper case version of you
case-insensitive field (e.g. upperCustomerName) and search on it
instead.  This may have changed with 5.0.  We're still using 4.2.

Quote
Cecile Gousseau wrote:

> Hello

> A colleague of mine is using WNT Workstation, D2 and Interbase (local).
> When executing a query that contains the following ' where customername
> like %boe% ' it appears that the results are case sensitive - that is
> 'Boeing' will not be reutrned by the query whereas 'boeing' will be. We
> need it to be NOT case sensitive...

> We pored over all the documentation we have, to no avail. We suspect
> that there is an Interbase parameter that drives this behaviour. Has
> anyone senn (and solved) this?

> Any help appreciated.

> Regards,

> Jean-Rene Nicolet
> for Clinton Langan

Re:Interbase: Case Sensitive ?


Quote
Cecile Gousseau wrote:
> Hello

> A colleague of mine is using WNT Workstation, D2 and Interbase
> (local).
> When executing a query that contains the following ' where
> customername
> like %boe% ' it appears that the results are case sensitive - that is
> 'Boeing' will not be reutrned by the query whereas 'boeing' will be.
> We
> need it to be NOT case sensitive...

> We pored over all the documentation we have, to no avail. We suspect
> that there is an Interbase parameter that drives this behaviour. Has
> anyone senn (and solved) this?

> Any help appreciated.

> Regards,

> Jean-Rene Nicolet
> for Clinton Langan

   You can use either:
where UPPER(CUSTOMERNAME) LIKE '%BOE%' - (this will slow down the query
very much)
or
where UPPER(CUSTOMERNAME) STARTING WITH 'BOE' (slow, too)
or
create an CUSTOMER_MATCH field in the table where ou store the name in
capital letters (if you don't use special characters, you can let this
be done by triggers in the INSERT and UPDATE event) and select with:

where CUSTOMER_MATCH >= 'BO' (or STARTING WITH) - this is the fastest
way I know.

Regards
Luc.

Other Threads