Re: Sensitive SQL Question
Tony Holder (Tony@robi.demon.co.uk) wrote:
: Is there any way to turn of case sensitivity for a simple query e.g.
: select * from clients where cliname matches "PHUCKIT*"
: I read in an earlier thread that you can turn off case sensitivity in
: a CONSTRUCT with "OPTIONS CONSTRUCT CASE INSENSITIVE" but the compiler
: just moans with a "-4373 A grammatical, blah blah blah ..." error, I
: checked the Informix manuals and there was no reference to it, Is it
: the version of RDS i am running?, There must be a way around this
: without retreiving every row from the table and then UPSHIFTing it
: before comparing!, Could stored procedures do it?
Yes, there is a way.
select * from clients where cliname matches "[Ss][Mm][Ii][Tt][Hh]*"
It will match "Smith" "SMITH" "sMiTh" etc...
The same "trick" can be used for the CONSTRUCT by including:
after field fieldName
let value = fgl_getfldbuf()
let value = formatValue(value)
display value to fieldName
A formatting function can easily be written:
Scan string
substitute every letter by [UPPERlower]
Caveat: you must handle actual [char] expressions that the user
may specify
Enjoy!
--
;,._..___...-''^``--._ |\\, Catalin Badea
```--,,,--, `-_ .`/ ,\\ Senior Technical Architect
{\\ ` ``-_' / } Tecsys Inc.
; ; ; `;;'
`.;. `.._ ,,,_. ``--.._. cat@tecsys.com
``--..,) ```----...._,) include system "disclaim.4gh"
(yeah, another NewEra fiend)