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 | +------+