Re: [Zope-DB] case insensitive ZSQL queries
Use MS-SQL or Sybase :o) ----- Original Message ----- From: "onsombal" <onsombal@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>
Providing your database has the upper() function, or replace with similar select ... <dtml-sqlgroup where> <dtml-if firstname> upper(firstname) like upper(<dtml-sqlvar firstname type=string>) </dtml-if> <dtml-or> <dtml-if lastname> upper(lastname) like upper(<dtml-sqlvar lastname type=string>) </dtml-if> <dtml-or> ...etc... </dtml-sqlgroup> On 06/09/07, Jaroslav Lukesh <lukesh@seznam.cz> wrote:
Use MS-SQL or Sybase :o)
----- Original Message ----- From: "onsombal" <onsombal@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@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 )
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@seznam.cz <mailto:lukesh@seznam.cz>> wrote:
Use MS-SQL or Sybase :o)
----- Original Message ----- From: "onsombal" <onsombal@yahoo.com <mailto:onsombal@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@zope.org <mailto:Zope@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@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
participants (3)
-
Jaroslav Lukesh -
Josh Burvill -
Peter Bengtsson