Re: Problem with Case Sensitivity in Oracle?

From: Kevin Neel <k-neel_at_nwu.edu>
Date: 4 Mar 1994 00:06:59 GMT
Message-ID: <2l5u33$515_at_anaxagoras.ils.nwu.edu>

In article <CM3CM5.KwD_at_wm-mercer.ca> Ramy Taraboulsi, tarabour_at_wm-mercer.ca writes:
>If you are executing SQL or PL/SQL statements, the way to turn the case
>sensitivity on or off is setting SQLC[ASE] to lower or to upper. Please
 notice
>that this could only be achieved through SQL*PLUS, that is, you could not
>set SQLC[ASE] this way in PRO*C, for example.
>
>A reveiew of the SQL*PLUS manual will further explain this.
>
>I hope this answers your question.

I don't think it does. I'm sure that this parameter has nothing to do with the case sensitivity of comparisons within a SQL statement; it probably has more to do with the case sensitivity of SQL*Plus command names and such.

There is a workaround, which I have previously posted. Here it is again:

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%' OR
        ename LIKE 'Fr%' OR
        ename LIKE 'fR%' OR
        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' if prefix matching is desired. In order to get 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)) || '%' ) OR
        ename LIKE ( UPPER( SUBSTR(:ename,1,1) ) ||
                     LOWER( SUBSTR(:ename,2,1)) || '%' ) OR
        ename LIKE ( LOWER( SUBSTR(:ename,1,1) ) ||
                     UPPER( SUBSTR(:ename,2,1)) || '%' ) OR
        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 Fri Mar 04 1994 - 01:06:59 CET