http://developer.mimer.com

Using Collations - Examples

---

The following sections contain examples of how to use collations and what effects collations can have on a result set.

The examples are based on the following (rather simple) table, table1:

colswe	coleng	col1
A	A	A
a	a	a
W	W	W
å	å	å
v	v	v

colswe uses the swedish collation, coleng uses the english collation and col1 uses the Mimer SQL default ISO8BIT collation.

ORDER BY

You can use a COLLATE clause together with an ORDER BY clause to sort result sets. For example, the following statement:

 SELECT *
    FROM table1
    ORDER BY col1 COLLATE SWEDISH;
 

retrieves the data and sorts it on col1 according to the swedish collation:

colswe	coleng	col1
A	A	A
a	a	a
W	W	W
v	v	v
å	å	å

Similarly, the following statement:

 SELECT *
    FROM table1
    ORDER BY col1 COLLATE ENGLISH;
 

retrieves the data and sorts it according to the english collation:

colswe	coleng	col1
A	A	A
a	a	a
å	å	å
v	v	v
W	W	W

Note: Where the collation defines a number of characters with the same sort-order value, the retrieval order within the sort-order value is not defined.

GROUP BY

Depending on the collation associated with a column, you might get differing results when using GROUP BY.

For example, the statement:

 SELECT col1, COUNT(*)
    FROM table1
    GROUP BY col1 COLLATE SWEDISH;
 

gives the following result:

col1
A	2
W	2
å	1

According to the swedish collation, two instances of the character 'a' were found and one instance of 'å' which is considered a separate character in the Swedish language.

Similarly, using the english collation in the statement:

 SELECT col1, COUNT(*)
    FROM table1
    GROUP BY col1 COLLATE ENGLISH;
 

gives the following result:

col1
A	3
v	1
W	1

According to the english collation, three instances of the character 'a' were found, as the character 'å' has the same sort-order value as 'A' and 'a'.

Comparison Operators

You can qualify the comparison operators (=, <>, <, <=, ...) with a COLLATE clause. For example:

 SELECT col1
    FROM table1
    WHERE col1 > 'm';
 

would give the following result:

col1
å
v

However, explicitly using the COLLATE clause and the english collation:

 SELECT col1
    FROM table1
    WHERE col1 > 'm' COLLATE ENGLISH;
 

would give the following result:

col1
W
v

Similarly, explicitly using the COLLATE clause and the swedish collation:

 SELECT col1
    FROM table1
    WHERE col1 > 'm' COLLATE SWEDISH;
 

would give the following result:

col1
W
å
v

Scalar String Functions

You can use the COLLATE clause with the scalar string functions SUBSTRING and TRIM.

Character strings that are derived from a single string, for example, those returned from the TRIM and SUBSTRING functions, inherit the collation from the source string.

TRIM and COLLATE

You should be aware of the consequences when you use a TRIM statement on a column that has a collation.

For example, referring to table1, see Using Collations - Examples, the following statement:

 SELECT TRIM('v' FROM colswe)
    FROM table1;
 

would trim both 'W' and 'v' from the result set as the characters 'W' and 'v' have the same sort-order value in a Swedish case-insensitive collation.

Similarly, the following statement:

 SELECT TRIM('a' FROM col1)
    FROM table1;
 

would trim 'A', 'a' and 'å' from the result set as the characters 'A', 'a' and 'å' have the same sort-order value in an english case-insensitive collation.

Concatenation Operator

Suppose you want to concatenate columns, colswe and col1, for example:

 SELECT colswe || coleng 
    FROM table1;
 

Because the columns use different collations the result set will have the default collation ISO8BIT.

However, if you want apply a collation to the result set, you can add a COLLATE clause. for example:

 SELECT (colswe || coleng) COLLATE SWEDISH 
    FROM table1;

IN and BETWEEN

A collation will affect the results of a query that uses IN or BETWEEN.

For example, the following statement:

 SELECT *
    FROM table1
    WHERE coleng BETWEEN 'a' and 'B';

returns:

colswe	coleng	col1
A	A	A
a	a	a
å	å	å

But, the next statement:

 SELECT *
    FROM table1
    WHERE colswe BETWEEN 'a' and 'B';
 

returns

colswe	coleng	col1
A	A	A
a	a	a

UNION

When performing a UNION, you must know what collations are involved in order to ensure that you get the result you want.

For example, the following statement:

 SELECT colswe 
    FROM table1
 
 UNION
 
 SELECT coleng 
    FROM table1;
 

returns:

colswe
A
W
a
v
å

This result has the Mimer SQL default collation ISO8BIT because both columns had different collations.

To apply a swedish collation to the result, you would explicitly use a COLLATE clause, for example:

 SELECT colswe 
    FROM table1
 
 UNION
 
 SELECT coleng COLLATE swedish 
    FROM table1;
 

which would return:

colswe
A
W
å

Similarly, for a result collated according to the english collation, you would enter:

 SELECT colswe COLLATE english 
    FROM table1
 
 UNION
 
 SELECT coleng 
    FROM table1;
 

which would return:

colswe
A
v
W

DISTINCT

When you use DISTINCT, you must consider the consequences of which collation is associated with a column.

In the following example:

 SELECT DISTINCT col1 
    FROM table1;
 

All entries in col1 are considered DISTINCT as it uses the Mimer SQL default collation ISO8BIT:

col1
A
W
a
v
å

However, in this next statement:

 SELECT DISTINCT colswe 
    FROM table1;
 

colswe uses the swedish collation. 'å' is considered to be distinct and 'v' and 'W' are not:

colswe
A
W
å

Similarly, in this example:

 SELECT DISTINCT coleng 
    FROM table1;
 

coleng uses the english collation, 'v' and 'W' are considered to be distinct and 'å' is not:

coleng
A
v
W

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se