# Information Schema COLLATIONS Table

The [Information Schema](/docs/server/reference/system-tables/information-schema.md) `COLLATIONS` table contains a list of supported [collations](/docs/server/reference/data-types/string-data-types/character-sets.md).

It contains the following columns:

| Column               | Description                                                                                                                                                                                                                                                                                                                                                   |
| -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| COLLATION\_NAME      | Name of the collation.                                                                                                                                                                                                                                                                                                                                        |
| CHARACTER\_SET\_NAME | Associated character set.                                                                                                                                                                                                                                                                                                                                     |
| ID                   | Collation id.                                                                                                                                                                                                                                                                                                                                                 |
| IS\_DEFAULT          | Whether the collation is the character set's default.                                                                                                                                                                                                                                                                                                         |
| IS\_COMPILED         | Whether the collation is compiled into the server.                                                                                                                                                                                                                                                                                                            |
| SORTLEN              | Sort length, used for determining the memory used to sort strings in this collation.                                                                                                                                                                                                                                                                          |
| PAD\_ATTRIBUTE       | <p>Determines whether or not trailing spaces are regarded as normal characters. See <a href="#no-pad-collations">below</a>.<br>Available from MariaDB 12.1.</p>                                                                                                                                                                                               |
| COMMENT              | <p>For utf8mb4\_0900 collations, contains the corresponding alias collation.</p><p>From <a href="/spaces/aEnK0ZXmUbJzqQrTjFyb/pages/hEJiNcmawlnvZVmjp4o9">MariaDB 11.4.5</a>, <a href="/spaces/aEnK0ZXmUbJzqQrTjFyb/pages/7FNi3DqrWSS5OFMbdhsD">MariaDB 11.7.2</a>, <a href="/spaces/aEnK0ZXmUbJzqQrTjFyb/pages/ZdbbdgxW6JXmvaqEdpPL">MariaDB 11.8.1</a>.</p> |

The [SHOW COLLATION](/docs/server/reference/sql-statements/administrative-sql-statements/show/show-collation.md) statement returns the same results and both can be reduced in a similar way.

{% tabs %}
{% tab title="Current" %}
The following two statements return the same results:

```sql
SHOW COLLATION WHERE Charset LIKE 'utf8mb3';
```

```sql
SELECT * FROM information_schema.COLLATIONS 
WHERE CHARACTER_SET_NAME LIKE 'utf8mb3';
```

{% endtab %}

{% tab title="< 10.5" %}
The following two statements return the same results:

```sql
SHOW COLLATION WHERE Charset LIKE 'utf8';
```

```sql
SELECT * FROM information_schema.COLLATIONS 
WHERE CHARACTER_SET_NAME LIKE 'utf8';
```

{% endtab %}
{% endtabs %}

## NO PAD Collations

`NO PAD` collations regard trailing spaces as normal characters. You can get a list of all `NO PAD` collations as follows:

{% tabs %}
{% tab title="Current" %}

```sql
SELECT collation_name FROM information_schema.COLLATIONS
WHERE pad_attribute = "NO PAD";  
+------------------------------+
| collation_name               |
+------------------------------+
| big5_chinese_nopad_ci        |
| big5_nopad_bin               |
...
```

{% endtab %}

{% tab title="< 12.1" %}

```sql
SELECT collation_name FROM information_schema.COLLATIONS
WHERE collation_name LIKE "%nopad%";  
+------------------------------+
| collation_name               |
+------------------------------+
| big5_chinese_nopad_ci        |
| big5_nopad_bin               |
...
```

{% endtab %}
{% endtabs %}

In comparisons, `NO PAD` collations evaluate to `0` (`FALSE`). Example:

```sql
SELECT 'a ' = 'a'; 
+------------+ 
| 'a ' = 'a' | 
+------------+
|          0 | 
+------------+
```

## PAD SPACE Collations

`PAD SPACE` collations pad strings to equal lengths in comparisons, so that the comparison evaluates to `1` (`TRUE`). Example:

```sql
SELECT 'a ' = 'a'; 
+------------+ 
| 'a ' = 'a' | 
+------------+
|          1 | 
+------------+
```

## Example

```sql
SELECT * FROM information_schema.COLLATIONS;
+------------------------------+--------------------+------+------------+-------------+---------+
| COLLATION_NAME               | CHARACTER_SET_NAME | ID   | IS_DEFAULT | IS_COMPILED | SORTLEN |
+------------------------------+--------------------+------+------------+-------------+---------+
| big5_chinese_ci              | big5               |    1 | Yes        | Yes         |       1 |
| big5_bin                     | big5               |   84 |            | Yes         |       1 |
| big5_chinese_nopad_ci        | big5               | 1025 |            | Yes         |       1 |
| big5_nopad_bin               | big5               | 1108 |            | Yes         |       1 |
| dec8_swedish_ci              | dec8               |    3 | Yes        | Yes         |       1 |
| dec8_bin                     | dec8               |   69 |            | Yes         |       1 |
| dec8_swedish_nopad_ci        | dec8               | 1027 |            | Yes         |       1 |
| dec8_nopad_bin               | dec8               | 1093 |            | Yes         |       1 |
| cp850_general_ci             | cp850              |    4 | Yes        | Yes         |       1 |
| cp850_bin                    | cp850              |   80 |            | Yes         |       1 |
...
```

## See Also

* [Setting Character Sets and Collations](/docs/server/reference/data-types/string-data-types/character-sets/setting-character-sets-and-collations.md) - specifying the character set at the server, database, table and column levels
* [Supported Character Sets and Collations](/docs/server/reference/data-types/string-data-types/character-sets/supported-character-sets-and-collations.md) - full list of supported characters sets and collations.

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-collations-table.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.