From: "lindawie" Newsgroups: comp.databases.oracle.server References: <3B608553.8AB24E54@dv-werk.de> <9jr2nl$hmk$1@mail.cn99.com> <9jrs1g$16b$1@reader-00.news.insnet.cw.net> <9jrurs07t2@drn.newsguy.com> <3B61BE85.D77078A6@attws.com> <9jsfvb02c8u@drn.newsguy.com> Subject: Re: turn off/on case insensetive search in Oracle DBMS Lines: 74 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 Message-ID: X-Trace: +SHLvkl/9ocjcxayuawp2kyVCVR02JyKEssJMaIUQaeiA255cR1Y9r9xvdK5u3/xy+NFDY1kZJwp!qqsfMhbWaLUiTlC/6flZ+Kq4x2MUzsfu99l+7QK7kdDI7ibsgKSWuX8vYPB3x+bJi1UEzH20DLT1!9D9oWh3i X-Complaints-To: abuse@gte.net X-Abuse-Info: Please be sure to forward a copy of ALL headers X-Abuse-Info: Otherwise we will be unable to process your complaint properly NNTP-Posting-Date: Sun, 05 Aug 2001 03:39:54 GMT Distribution: world Date: Sun, 05 Aug 2001 03:39:54 GMT 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