Forcing case insensitivity

Forcing case insensitivity

Post by Steve Catmul » Thu, 15 Jul 1999 04:00:00



Is there a way to force a database or named source to force case
insensitivity?  The reason that I ask, is that I used to work with a
personal version of SQL Anywhere and it had the option to force case
insensitiviy when the database file was created.

I talked with the DBAs in my company, but nobody seemed to be familiar
with any method.  They all thought that the SELECT statements would have
to be modified to use the upper function.  This is not too feasible
because I am dealing with an client app that will not offer that type of
configurability.

By the way, the Oracle versions in question are 7.3.3 and 8.0.x.

Thanks,

Steve Catmull
IHC

 
 
 

Forcing case insensitivity

Post by Martin Dougla » Mon, 19 Jul 1999 04:00:00


I have just recently spoken to our DBA and he had similar bad news. The
problem is that you cannot do the following...

create table x(
  y varchar2(2000),
  constraint unq_ci_y unique(upper(y))
);

In light of this limitation, there are several alternatives...

1) Use a stored procedure to first check for the existence of
   a case insensitive record using

   select count(y) into v_cnt from x where upper(y) = upper(p_y);

   And then if v_cnt is 0 proceed with the insert.

   This sort of thing is required when you need to store records
   that need to be unique yet blind to case.  Oracle's unique
   constraint considers 'ABC', 'abc', 'aBc',... all to be
   different and thus inserts will succeed.  However, this may
   not always be what you want.  And casting to uppercase may
   not be a valid solution if you want to store the information
   as provided and not in upper case.  This is definately true
   in my application where I need to store the names of variables
   in a table and do so WYSIWYG-like. The variables 'n' and 'N'
   are the same thing so one must be rejected (hopefully by a
   unique constraint).  I want to store either 'n' or 'N' as
   supplied, but not both.  So I resort to the stored procedure
   to do Oracle's work for it.

2) The other solution is similar and involves the use of triggers
   if you are in a anti-stored-procedure shop.

Either way, it is an oversight on Oracle's part to assume that
developers have no need to be able to reject a record based on
case rather than just casting to upper or lower and accepting
it thus for storage.


> Is there a way to force a database or named source to force case
> insensitivity?  The reason that I ask, is that I used to work with a
> personal version of SQL Anywhere and it had the option to force case
> insensitiviy when the database file was created.

> I talked with the DBAs in my company, but nobody seemed to be familiar
> with any method.  They all thought that the SELECT statements would have
> to be modified to use the upper function.  This is not too feasible
> because I am dealing with an client app that will not offer that type of
> configurability.

> By the way, the Oracle versions in question are 7.3.3 and 8.0.x.

> Thanks,

> Steve Catmull
> IHC