Re: Making an Oracle Database Instance Case - Insensitive

On 30 Dec 1997 18:19:00 GMT, "Nirav Saraiya" <nsaraiya_at_netright.com> wrote:

>I would like to make an oracle database use case insensitive data
>storage so that the following query will work
>
> select col1 from table1 where col1 = 'sanjay kumar'
>
> col1 may have 'SANJAY KUMAR' or 'Sanjay Kumar' but everything
>will be matched by the above select statement. There should be some
>switch/init parameter that I can set to make the database case insensitive.

I am not aware of any "switch" that you can use to control this behavior. There are a couple things I can suggest. The first is to use the UPPER() function in your where clause, however that may force a query to do a tablescan rather than use an index. My second idea would be to create a shadow column with the name in all-caps. This shadow column could easily be maintained by a trigger, and you could index on it as well.

Jonathan Received on Sat Jan 03 1998 - 00:00:00 CST