Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: how to configure my database or server to be case-insensitive

Re: how to configure my database or server to be case-insensitive

From: Jyotheesh Sukumaran <jsukumar_at_isomedia.com>
Date: 1998/10/28
Message-ID: <36376586.73A312EF@isomedia.com>#1/1

Hi,

I looked at the different responses you got and came up with combined idea. Here it is -

  1. Create an extra column for every column that you need as case insensitive.
eg:-              original table EMP_SALARY                       modified
table EMP_SALARY
                              name
char(20)                                          name char(20)
                              salary
number(10)                                      salary number(10)

name_upper char(20)

2. Create an insert/update trigger which also inserts/updates the extra column with only uppercase

    value of the original column (the original column gets the initially inserted value).

3. When doing a query do it against the extra column. But if the value of that column has to be pulled out,

    pull out the original column.

eg:-       select name, salary
            from EMP_SALARY
            where name_upper = upper('John');


Thanks
Jyotheesh

Rick Lin wrote:

> Hi,
>
> Is there anyway to configure my database or server to be case-insensitive?
> In other words, I would like my database or server to ignore case while
> doing
> text literal comparsion in all SQL statements. For example, the following
> criteria in where clause will return the same result:
> SELECT * FROM user_table WHERE use_name= 'john'
> SELECT * FROM user_table WHERE use_name= 'John'
> SELECT * FROM user_table WHERE use_name= 'JOHN'
> SELECT * FROM user_table WHERE use_name= 'jOhn'
>
> Thanks,
>
> Rick Lin
> Diffusion, Inc.
> rlin_at_diffusion.com
Received on Wed Oct 28 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US