[Zope] Re: [Zope-DB] case insensitive ZSQL queries

[Peter Bengtsson]

>      upper(lastname) like upper(<dtml-sqlvar lastname type=string>)
>    
* Don't use LIKE without any regular expression

* Use ILIKE instead of UPPER(...) = UPPER(...) since it's usually faster 
(I've "proven" this for PostgreSQL at least)

* Fastest method is always to use an index and you can write an index 
based on the UPPER() of a column. I've done this for a postcode table 
and sped up my queries by more than 10 times.


> On 06/09/07, *Jaroslav Lukesh* < lukesh at seznam.cz 
> <mailto:lukesh at seznam.cz>> wrote:
> 
>     Use MS-SQL or Sybase :o)
> 
>     ----- Original Message -----
>     From: "onsombal" <onsombal at yahoo.com <mailto:onsombal at yahoo.com>>
>      >
>      > is there an easy way to make the following ZSQL query
>     case-insensitive?
>      >
>      > select * from clients
>      > <dtml-sqlgroup where>
>      >    <dtml-sqltest firstname op="like" type="string" optional>
>      >  <dtml-or>
>      >    <dtml-sqltest lastname op="like" type="string" optional>
>      >  <dtml-or>
>      >    <dtml-sqltest client op="like" type="string" optional>
>      >  </dtml-sqlgroup>
>      >
>     _______________________________________________
>     Zope maillist  -   Zope at zope.org <mailto:Zope at zope.org>
>     http://mail.zope.org/mailman/listinfo/zope
>     **   No cross posts or HTML encoding!  **
>     (Related lists -
>     http://mail.zope.org/mailman/listinfo/zope-announce
>     http://mail.zope.org/mailman/listinfo/zope-dev
>     <http://mail.zope.org/mailman/listinfo/zope-dev> )
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Zope maillist  -  Zope at zope.org
> http://mail.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists - 
>  http://mail.zope.org/mailman/listinfo/zope-announce
>  http://mail.zope.org/mailman/listinfo/zope-dev )

-- 
Peter Bengtsson,
work www.fry-it.com
home www.peterbe.com
hobby www.issuetrackerproduct.com