COLLATE

MySQL

Example

SELECT
  column1 COLLATE utf8_unicode_ci AS NewColumnName
FROM
  table_name;

Output

NewColumnName
Data
data
DATA

Explanation

In the COLLATE clause, we have specified utf8_unicode_ci as the collation, which makes the search case-insensitive. Thus, on executing the query, MySQL fetches the data in column1 transformed into a case-insensitive format under a new column called NewColumnName.

PostgreSQL

Example

SELECT name, country
FROM   customers
ORDER  BY name COLLATE "en_US";

Output

| name | country  |
|------|----------|
| Anna | Germany  |
| Bob  | USA      |
| Carl | Australia|

Explanation

In this code snippet, ‘COLLATE “en_US”’ changes the sort order by collation of the ‘name’ column. The names are sorted using lexicographic order following the English language rules. For example, upper and lower case letters are considered to be equal.

SQL Server

Example:

SELECT Name
FROM Employee
ORDER BY Name COLLATE SQL_Latin1_General_CP1_CI_AS;

Output:

'Erik'
'John'
'Natalie'
'Sarah'
'Zach'

Explanation

The COLLATE clause in SQL is used for defining the sorting order of specific columns, which can be helpful in dealing with strings from different languages and sets. In this specific example, the COLLATE clause is used for ordering Employee names in case-insensitive ascending order. SQL_Latin1_General_CP1_CI_AS is a Latin General, case-insensitive, accent-sensitive, kanatype-insensitive collation.

Oracle

Example

SELECT *
FROM (
    SELECT 'A' as Letter UNION ALL
    SELECT 'a' UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'b'
)
ORDER BY Letter COLLATE BINARY_CI;

Output

A
a
B
b

Explanation

The COLLATE clause in Oracle allows you to specify the collation used for sorting the results. This SQL statement shows case-insensitive sorting using the BINARY_CI collation, where ‘A’ and ‘a’ are considered the same for sorting purposes.