helpinghand
search
needassistance
 
Collation FAQ
1. How do I present my existing Character data sorted in my own language (with pre-defined support in Mimer SQL)?

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.


2. I need to efficiently sort my texts in several languages in the same application. How?

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
						
3. My language is not included in the pre-defined set. How can I use the definition available for download from the Mimer SQL web site?

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<ng<<<Ng<<<NG'
SQL>'&W<wh<<<Wh<<<WH';
						
4. My language is neither pre-defined nor available for download. Can I define the Collation myself?

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';
						
5. I want to change the sort order in a pre-defined language. How?

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 '&#0300#<<#0309#<<#0303#<<#0301#<<#0323#';
						
6. How do I make language sensitive searches?

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
						
7. Are contractions handled correctly in Mimer SQL?

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
						
8. I've stored numerical data as Character in the database. How can I have it sorted as numbers, not text (i.e. "10" should be greater than "9 ")?

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
						
9. How do I ignore prefixes like "de" and "van" in my sorted list of names?

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
						

 

Powered by Mimer SQL

Powered by Mimer SQL