RE: Case insensitive search
Jay:
> We have Informix OWS 7.22 for NT and I'm wondering if there is a way to =
do
> case-insensitive searches.
An alternative is to have an SPL that creates the matching clause, rather =
than performing the function on the column. In other words, your SQL =
would read:
SELECT custname
FROM customer
WHERE custname LIKE nocase("CON%")
... and the nocase() function returns:
"[Cc][Oo][Nn]%"
You should be able to hack your UPPER function to do this. Theoretically, =
the Optimiser should only call the SPL once, not on every row, since its =
argument is a constant. Whether this happens or not cannot be determined =
from sqexplain.out
One interesting point, though: an explicit
ORDER BY custname
does force an INDEX PATH search.
It is always difficult getting good performance out of LIKE or MATCHES, =
and any query which involves SPL is usually substantially slower than one =
that does not. In any case, performing the UPPER function on the column =
will mean the index cannot be used.
cheers
RET
+--------------------------------------------------------------------------=
----+
| Richard Thomas (DBA) richard_thomas@yes.optus.com.au +61 2 9342 =
7188 |
+--------------------------------------------------------------------------=
----+