Re: Case-insensitivity

Steve Muench suggested a method of performing case-insensitive indexed lookups without having to store an additional single-case version of the mixed-case data (see attachment).

I just tried it on a table we have with 33000 rows containing mixed-case data and it worked well. Using an index on a single-case version of the data took 0.06 secs of CPU time, and using Steve's technique took 0.15 secs of CPU time (with ORACLE 6 on a MicroVAX 3100 if that makes the numbers any more useful).

Thanks!

(Though dBASE or SQL Server style options which make the case-insensitivity transparent to the application programmer would be even better :-).

> DAVID -- Say you are wanting to search case-insensitively on a column
> named ENAME for a string entered as 'June'. Then, just to be
> clear, that means you'd want to locate:
>
> June
> JUne
> JUNe
> JUNE
> june
> jUne
> :
> junE
>
> If you want to still use the index on ENAME, you could phrase
> your query such that -- in addition to the UPPER(ENAME) =
> UPPER('June') clause -- you also try the four permutations of
> the first two letters in the word. Like:
>
> WHERE ((ENAME LIKE 'JU%' OR
> ENAME LIKE 'Ju%' OR
> ENAME LIKE 'jU%' OR
> ENAME LIKE 'ju%')
> AND UPPER(ENAME) = UPPER('June'))
>
> You obviously could take permutations on the first three
> letters, or more, but two letters typically makes the index
> selective enough to be much more useful than a full-table
> scan.
>
> The generic code to prepare the "four-way" or clause above
> for any string 2 letters or longer would be fairly
> straightforward to put together. I just wanted to suggest the
> technique to you.
>
> Hope this is of assitance.
> --
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> Steve Muench Email: smuench_at_oracle.com
> Forms Development
> Product Manager
Received on Thu Nov 11 1993 - 11:52:10 CET