Re: turn off/on case insensetive search in Oracle DBMS

Tom,

> ) the feature in SQL Server whereby a database can be built with case
> insensitive serching or not. For them, its all or nothing at the db level
 (its
> not a runtime switch one and off -- its an attribute of a database). For
 us,
> its a choice. In fact you can have your cake and eat it too:

Your information is outdated. SQL Server allows you to specify the collation, which also determines case sensitivity, at the server, database (=schema), table, and column level in DDL. It is also possible to use SQL collations in DML statements as part of string expressions, for example in predicates, to restrict on, group by, or order by case sensitive data values. There are over 750 different SQL collations to choose from so you can mix and match within a single statement if need be.

If you want absolute flexibility and are willing to take a possible performance hit, you can construct and execute statements dynamically using a SQL collation supplied at runtime. Not very practical in most cases, but definitely doable.

For example,

create table abc (

    abcid int identity primary key,
    label1 varchar(10) not null
)

insert abc (label1) values ('Arno')
insert abc (label1) values ('ARNO')
insert abc (label1) values ('arno')
insert abc (label1) values ('Bärbel')
insert abc (label1) values ('bärbel')
insert abc (label1) values ('BÄRBEL')
insert abc (label1) values ('Barbara')
insert abc (label1) values ('barbara')

• Case-Sensitive, Accent-Insensitive
• Restrict on exact case-sensitive matches. select abcid, label1 from abc where label1 collate Latin1_General_CS_AI in ('arno', 'BÄRBEL', 'barbara') order by label1 collate German_PhoneBook_CS_AI
• Case-Insensitive, Accent-Insensitive
• Restrict on case-insensitive matches. select abcid, label1 from abc where label1 collate Latin1_General_CI_AI in ('arno', 'BÄRBEL', 'barbara') order by label1 collate German_PhoneBook_CS_AI

abcid label1
----------- ----------

3           arno
6           BÄRBEL
8           barbara

abcid       label1
----------- ----------
3           arno
1           Arno
2           ARNO
5           bärbel
4           Bärbel
6           BÄRBEL
8           barbara
7           Barbara

Linda Received on Sat Aug 04 2001 - 22:39:54 CDT