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_1 collation, coleng uses the english_1 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_1;
 

retrieves the data and sorts it on col1 according to the swedish_1 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_1;
 

retrieves the data and sorts it according to the english_1 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_1;
 

gives the following result:

col1
A	2
W	2
å	1

According to the swedish_1 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_1 collation in the statement:

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

gives the following result:

col1
A	3
v	1
W	1

According to the english_1 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_1 collation:

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

would give the following result:

col1
W
v

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

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

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_1
    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;

raises an error becuase the UNION operator can't understand which duplicate rows to remove or not.

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

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

which would return:

colswe
A
W
å

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

 SELECT colswe COLLATE english_1
    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_1 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_1 collation, 'v' and 'W' are considered to be distinct and 'å' is not:

coleng
A
v
W

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se