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