Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Can Oracle8 Support This?
Oracle 8i supports indexes with functions. This means you can index
UPPER(col1).
On Fri, 30 Apr 1999 08:29:28 GMT, jpetts_at_celltech.co.uk (James Petts) wrote:
>On Thu, 29 Apr 1999 13:51:06 GMT, "Scott J. Hamilton"
><ScottHamilton_at_home.com> wrote:
>
>>Does anyone know if Oracle8 supports case insensitive primary indexes? And
>>if it does, can you give me an example?
>
>If you want case-insensitvity you will need to use an UPPER() or
>LOWER() function somewhere. Unfortunately, these functions will
>disable the use of indices.
>
>However, what you could do (if space isn't an issue) is to use as the
>primary key a case-desensitised version of what you are really wanting
>to use as the PK. Then, when a vlaue is inserted into that column, use
>a before insert trigger to convert it to upper case and insert the
>converted value into the column which is the real primary key (and
>hence is indexed) of the table.
>
>When you do a search on the primary key, you can alsoe return the
>value from the column which contains the mixed case entry.
>
>James Petts
Received on Fri Apr 30 1999 - 10:41:09 CDT