Re: Oracle 7.0: Case insensitive string comparisons?
Date: 18 Jan 1994 21:07:17 GMT
Message-ID: <2hhj25$bid_at_anaxagoras.ils.nwu.edu>
In article <des.18.2D3BF0EE_at_helix.nih.gov> David E. Scheim,
des_at_helix.nih.gov writes:
>I'm sure there must be a neat solution to this problem which an expert
in
>Oracle can point out. In Sybase or its PC implementation, MS SQL
Server,
>one can define all character strings to be stored case sensitive but
>searched case insensitive, as an option at installation. Most of our
fields
>are character strings we wish to store/search in this fashion, and I
could
>not conceive of operating our system without such automatic case
handling
>capability. -- David Scheim
There is a workaround invented in the Applications Division (of which I
was a
member); it's not quite as neat as one might like, but it works well
enough
for interactive use. Create a statement with 2^N ORed LIKE clauses for
checking the first N characters of the string -- one case for each
combination of upper or lower case for those characters -- and AND those
clauses with an UPPER vs. UPPER check. Usually, for decent indexing the
first character or two is enough (I believe we used two).
For example, suppose that one is looking up Fred as an ENAME in the EMP table (what else? :). The following statement works pretty well:
SELECT ename, empno FROM emp
WHERE (ename LIKE HFR% ename LIKE HFr% ename LIKE HfR% ename LIKE Hfr%
AND UPPER(ename) = HFRED
Some variations on this technique are of course possible. For instance,
one would likely want to replace the quoted strings above with variables,
and store the appropriate values in those variables during cursor open --
thus avoiding reparse etc. And one might want to use a LIKE HFRED%
instead of the = HFRED
the NLS features of the RDBMS, you may even want to expand those strings
into expressions that do the appropriate calculations -- something like
SELECT ename, empno FROM emp
WHERE (ename LIKE ( UPPER( SUBSTR(:ename,1,1) ) || UPPER( SUBSTR(:ename,2,1)) || H% ename LIKE ( UPPER( SUBSTR(:ename,1,1) ) || LOWER( SUBSTR(:ename,2,1)) || H% ename LIKE ( LOWER( SUBSTR(:ename,1,1) ) || UPPER( SUBSTR(:ename,2,1)) || H% ename LIKE ( LOWER( SUBSTR(:ename,1,1) ) || LOWER( SUBSTR(:ename,2,1)) || H%AND UPPER(ename) LIKE UPPER(:ename) || H%
Since those operations require no I/O, they are pretty cheap, even if done by the RDBMS.
Oracle Application Object Library (OAOL) has used these techniques in implementing QuickPick (in fact, the invention led to the name!); and I have been told that SQL*Forms 4.0 has adopted this technique as an option -- you can check the 'case-insensitive query' attribute on fields to use it in query-by-example. Received on Tue Jan 18 1994 - 22:07:17 CET