Re: Oracle 7.0: Case insensitive string comparisons?

From: Kevin Neel <k-neel_at_nwu.edu>
Date: 18 Jan 1994 22:04:58 GMT
Message-ID: <2hhmea$c4i_at_anaxagoras.ils.nwu.edu>

Well, I'm still figuring out how to use my new netnews environment (apparently not everything on the Mac is obvious, at least when interacting with the UNIX world). Here's what I meant:

In article <2hhj25$bid_at_anaxagoras.ils.nwu.edu> Kevin Neel, k-neel_at_nwu.edu writes:
>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 'FR%'
        ename LIKE 'Fr%'
        ename LIKE 'fR%'
        ename LIKE 'fr%'

   AND UPPER(ename) = 'FRED'

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 'FRED%' instead of the = 'FRED'
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)) || '%'
        ename LIKE ( UPPER( SUBSTR(:ename,1,1) ) ||
                     LOWER( SUBSTR(:ename,2,1)) || '%'
        ename LIKE ( LOWER( SUBSTR(:ename,1,1) ) ||
                     UPPER( SUBSTR(:ename,2,1)) || '%'
        ename LIKE ( LOWER( SUBSTR(:ename,1,1) ) ||
                     LOWER( SUBSTR(:ename,2,1)) || '%'

   AND UPPER(ename) LIKE UPPER(:ename) || '%'

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 - 23:04:58 CET