SQL Reference - COLLATE

COLLATE

COLLATE in SQL is a clause used to set the specific type of sorting order in a SQL statement. It determines the rules that SQL Server uses for sorting and comparison of data within a particular expression or column. The COLLATE clause can be used to customize a column's collation to meet specific needs. It controls the way in which data is sorted and compared based on the rules of the linguistic sort order defined by the collation.

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.

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.

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.

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.