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