Re: Oracle vs Sybase

> You can use a trigger on update for every varchar field (select
> upper(:new.field_name) into :new.field_name from dual;)

This would not work because I want to keep the data in a case sensitive manner for reporting purposes.

> or if you want to preserve your case sensitive data and ONLY search case insensitive
> you can use where upper(field_name) like upper'your_serach_string%'
> To increase performance aou can use the new feature of 8i, function based indexes
> (create index ...upper(field_name))

These would work except that I already have about 1400 queries saved in access. I really really don't want to rewrite every single one of them. I have been digging into the oracle documentation a bit and NLS_COMP and NLS_SORT parameters seem like it's what I want to do. They both seem to have som quirks though. Here is what the docs say.

"As a final note, when NLS_COMP is set to ANSI, a linguistic index must exist on the column where the linguistic order is desired."

This means I have to build a index on every single text field? why isn't possible to tell oracle just to treat all text as US english (not even an option!) or Ascii7 (what does that mean?) and treat all sorts and order by statements in a case insensitive manner?. In sybase this is possible when you create the database and in interbase this is possible in when you create the column. Received on Tue Mar 28 2000 - 00:00:00 CST