COLLATE function

The COLLATE function overrides the collation of a string column or expression.

For information on how to create tables using database collation, see CREATE TABLE.

For information on how to create databases using database collation, see CREATE DATABASE.

Syntax

COLLATE( string, 'case_sensitive' | 'case_insensitive');

Arguments

string

A string column or expression that you want to override.

'case_sensitive' | 'case_insensitive'

A string constant of a collation name. Amazon Redshift only supports case_sensitive or case_insensitive.

Return type

The COLLATE function returns VARCHAR or CHAR depending on the first input expression type. This function only changes the collation of the first input argument and won't change its output value.

Examples

To create table T and define col1 in table T as case_sensitive, use the following example.

CREATE TABLE T ( col1 Varchar(20) COLLATE case_sensitive );

INSERT INTO T VALUES ('john'),('JOHN');

When you run the first query, Amazon Redshift only returns john. After the COLLATE function runs on col1, the collation becomes case_insensitive. The second query returns both john and JOHN.

SELECT * FROM T WHERE col1 = 'john';

+------+
| col1 |
+------+
| john |
+------+

SELECT * FROM T WHERE COLLATE(col1, 'case_insensitive') = 'john';

+------+
| col1 |
+------+
| john |
| JOHN |
+------+

To create table A and define col1 in table A as case_insensitive, use the following example.

CREATE TABLE A ( col1 Varchar(20) COLLATE case_insensitive );

INSERT INTO A VALUES ('john'),('JOHN');

When you run the first query, Amazon Redshift returns both john and JOHN. After the COLLATE function runs on col1, the collation becomes case_sensitive. The second query returns only john.

SELECT * FROM A WHERE col1 = 'john';

+------+
| col1 |
+------+
| john |
| JOHN |
+------+

SELECT * FROM A WHERE COLLATE(col1, 'case_sensitive') = 'john';

+------+
| col1 |
+------+
| john |
+------+