FAQ

Yes, here is an example:

SQL>-- In Danish AA is a contraction equal to Å
SQL>create table t(name varchar(10));
SQL>insert into t values(U&'Aller\00F8d');
SQL>insert into t values('Aalborg');
SQL>insert into t values('Århus');
SQL>select * from t where name like 'A%' collate danish_1;

NAME 
==== 
Allerød

SQL>select * from t where name like 'Å%' collate danish_1;

NAME 
==== 
Aalborg
Århus

SQL>select * from t where name like 'AA%' collate danish_1;

NAME 
==== 
Aalborg
Århus

See the following example:

SQL>create table t (names varchar(10));
SQL>insert into t values('van Gogh');
SQL>insert into t values('van Damme');
SQL>insert into t values('Janssen');
SQL>insert into t values('Ebermann');
SQL>insert into t values('de Luge');

SQL>-- Normal order using EOR default
SQL>select * from t order by names collate eor;

NAMES
==========
de Luge
Ebermann
Janssen
van Damme
van Gogh

SQL>-- Customize the EOR default collation
SQL>create collation ignore_devan from eor
SQL&using '[level 4][alternate shifted]&" "<<<de" "<<<van" "'; SQL>select * from t order by names collate ignore_devan;

NAMES
==========
van Damme
Ebermann
van Gogh
Janssen
de Luge

Here are some examples:

SQL>create table t (word varchar(10));
SQL>insert into t values('COOP');
SQL>insert into t values('coop');
SQL>insert into t values('COÖP');
SQL>insert into t values('coöp');
SQL>insert into t values('CO-OP');
SQL>insert into t values('co-op');
SQL>insert into t values('CO OP');
SQL>insert into t values('co op');
SQL>insert into t values('COTE');
SQL>insert into t values('COTÉ');
SQL>insert into t values('CÔTE');
SQL>insert into t values('CÔTÉ');
SQL>insert into t values('cote');
SQL>insert into t values('coté');
SQL>insert into t values('côte');
SQL>insert into t values('côté');
SQL>
SQL>-- Accent and case insensitive search (level 1):
SQL>select word from t where word = 'côte' collate french_1
SQL>order by word collate French_3;

WORD
====
cote
COTE
côte
CÔTE
coté
COTÉ
côté
CÔTÉ

SQL>-- Accent sensitive and case insensitive search (level 2):
SQL>select word from t where word = 'côte' collate french_2
SQL>order by word collate French_3;

WORD
====
côte
CÔTE

SQL>-- Accent and case sensitive search (level 3):
SQL>select word from t where word = 'côte' collate french_3
SQL>order by word collate French_3;

WORD
====
côte

SQL>-- The predefined collations does not ignore punctuation.
SQL>-- Let's define collations that ignore punctuation and
SQL>-- an accent, case, and punctuation sensitive collation (level 4).
SQL>create collation french_1s from french_1 using '[alternate shifted]';
SQL>create collation french_2s from french_2 using '[alternate shifted]';
SQL>create collation french_3s from french_3 using '[alternate shifted]';
SQL>create collation french_4 from french_3s using '[level 4]';
SQL>
SQL>-- Accent and case insensitive search (level 1):
SQL>select word from t where word = 'co-op' collate french_1s
SQL>order by word collate French_4;

WORD
====
co op
co-op
coop
CO OP
CO-OP
COOP
coöp
COÖP

SQL>-- Accent sensitive and case insensitive search (level 2):
SQL>select word from t where word = 'co-op' collate french_2s
SQL>order by word collate French_4;

WORD
====
co op
co-op
coop
CO OP
CO-OP
COOP

SQL>-- Accent and case sensitive search (level 3):
SQL>select word from t where word = 'co-op' collate french_3s
SQL>order by word collate French_4;

WORD
====
co op
co-op
coop

SQL>-- Accent, case, and punctuation sensitive search (level 4):
SQL>select word from t where word = 'co-op' collate french_4
SQL>order by word collate French_4;

WORD
====
co-op

For example, sorted in traditional Spanish order:

SQL>SELECT chardata FROM t ORDER BY chardata COLLATE spanish_traditional_3;

Observe that character data uses the ISO/IEC 8859-1 (Latin-1) encoding. If your data have used another encoding, you must translate it to Unicode and use the NCHAR datatype instead.

For example, sorting Swedish names in either Swedish or English (accent and case sensitive):

SQL>create table t (names nvarchar(30));
SQL>insert into t values('Arlanda');
SQL>insert into t values('Örebro');
SQL>insert into t values('Oskarshamn');
SQL>insert into t values('Ängelholm');
SQL>insert into t values('Malmö');
SQL>insert into t values('Stockholm');
SQL>insert into t values('Åmål');

SQL>-- You can speed up the sort with indexes!
SQL>create index s on t(names collate swedish_3);
SQL>create index e on t(names collate english_3);

SQL>SELECT names FROM t ORDER BY names COLLATE swedish_3;

NAMES 
===== 
Arlanda
Malmö 
Oskarshamn
Stockholm 
Åmål 
Ängelholm 
Örebro

SQL>SELECT names FROM t ORDER BY names COLLATE english_3;

NAMES 
===== 
Åmål 
Ängelholm 
Arlanda 
Malmö 
Örebro 
Oskarshamn
Stockholm

For example, changing the sort order for tone marks in Vietnamese to GRAVE, HOOK ABOVE, TILDE, ACUTE, and DOT BELOW:

SQL>CREATE COLLATION new_vietnamese_3 FROM vietnamese_3
SQL>USING 'Ĭ#<<#0309#<<#0303#<<#0301#<<#0323#';

Look at the following example:

SQL>create table alphanum (codes varchar(10));
SQL>insert into alphanum values('A123');
SQL>insert into alphanum values('A234');
SQL>insert into alphanum values('A23');
SQL>insert into alphanum values('A3');
SQL>insert into alphanum values('A1');

SQL>-- Normal order [Numeric Off]
SQL>select * from alphanum order by codes;

CODES
==========
A1
A123
A23
A234
A3

SQL>-- Numeric order [Numeric On]
SQL>create collation numeric from eor using '[Numeric On]';
SQL>select * from alphanum order by codes collate numeric;

CODES
==========
A1
A3
A23
A123
A234

Of course, let us define a collation for Sango (with orthography from http://www.rosettaproject.org):

SQL>CREATE COLLATION sango FROM eor USING
SQL>'&G<gb<<<Gb<<<GB' SQL>'&K<kp<<<Kp<<<KP' SQL>'&M<mb<<<Mb<<<MB<mv<<<Mv<<<MV' SQL>'&N<nd<<<Nd<<<ND<ng<<<Ng<<<NG<ngb<<<Ngb<<<NGB' SQL>'<ny<<<Ny<<<NY<nz<<<Nz<<<NZ';

For example, click on Maori to get the Maori Collation Chart, then click on Collation to get the CREATE COLLATION statement:

SQL>CREATE COLLATION maori FROM eor USING
SQL>'&N'&W