 ## 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.

### 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
```

### ORDER BY

You can use a `COLLATE` clause together with an `ORDER BY` clause to sort result sets. In most cases a level 3 collation is suitable for order by purposes. For example:

``` SELECT *
FROM table1
ORDER BY col1 COLLATE swedish_3;

```

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

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

Similarly, the following statement:
``` SELECT *
FROM table1
ORDER BY col1 COLLATE english_3;

```

retrieves the data and sorts it according to the `english_3` collation:

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

### 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'.

### 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 statement:

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

```

returns

``` colswe
```
``` coleng
```
``` col1
```
``` A
```
``` A
```
``` A
```
``` a
```
``` a
```
``` a
```

### UNION, EXCEPT and INTERSECT

When performing a `UNION` (or `EXCEPT` or `INTERSECT`), 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 because the `UNION` operator can't understand which duplicate rows to remove or not.

To perform the `UNION` according to the `swedish_1` collation, 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 `UNION` result 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. `å' and `A' are considered to be distinct, but `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, but `å' and `A' are not:

``` coleng
```
``` A
```
``` v
```
``` W
``` Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se