[Zope-DB] Making case-insensitive in sqlgroup

Philip Kilner phil at xfr.co.uk
Wed Nov 3 11:17:51 EST 2004


Hi Joshua,

Joshua Burvill wrote:
> Hello to everyone,
> 
> I am using zope 2.6.4, firebird 1.0, and kinterbasdbda and I would like to
> make the following query case-insensitive.
> 
> <dtml-sqlgroup where>
> ...
> <dtml-sqltest location op=eq type=nb optional>
> <dtml-and>
> ...
> </dtml-sqlgroup>
> 
> 
> If I was not using the convenient sqlgroup syntax, I would probably use
> something like:
> 
> where upper(location) = upper( <dtml-sqlvar location type=string> )
> 
> 
> Does anyone know how to do this?
> 

I've had exactly the same problem to solve - but in my case, I was able 
to provide a wild card parameter for every test, and not use the 
sqlgroup trick.

However, if you need to have optional parameters, you can do what you 
need by using an "expr" on the /parameter/ side of the equation to force 
your parameter into upper case and querying a /view/ in which you have 
/already/ forced the data into upper case, so instead of testing against 
"location" in T_LOC, you might test against "location_as_upper" in V_LOC.

The docs about the use of "exr" are in the Zope book - RDBMS chapter and 
DTML appendix.

HTH!


-- 

Regards,

PhilK

Email: phil at xfr.co.uk / Voicemail & Facsimile: 07092 070518

"Work as if you lived in the early days of a better nation." - Alasdair Gray


More information about the Zope-DB mailing list