A) What is collate?
The collate function in Snowflake allows specifying alternative rules for comparing strings.
B) What is the purpose of collate in Snowflake?
The collate function in Snowflake is used to compare and sort the data. The comparison and sorting will be based on a particular language or other user-specified rules.
The text strings in Snowflake are stored using UTF-8 character set. Comparing based on Unicode will not provide the desired output because of the following reasons :
1. The special character in a language does no sort based on the language standards.
2. In case we would like to achieve sorting based on special rules .e.g case insensitive sort.
C) What type of rules can be used with collate in Snowflake
Here is a list of rules that can be used with collate
1. Different character sets for different language
2. To achieve case insensitive comparisons
3. Accent sensitivity e.g a,á,ä
4 . Punctuation sensitivity e.g P-Q-R and PQR
5. Sorting based on the first letter in the strings.
6. Trimming leading and trailing spaces and then sorting
7. Other options can be implemented based on business needs.
D) Where to use collate in Snowflake SQL?
1. Simple comparison in where clause
WHERE FIELD1= FIELD2
2. Join condition
ON EMP. EMP_NM =MANAGER.MNGR_NM
3. Sorting condition
ORDER BY FIELD 1
4. Aggregation condition
5. Aggregate functions
MAX ( FIELD1)
6. Scaler functions
LEAST (FIELD1, FIELD2, FIELD3)
7. Data clustering conditions
CLUSTER BY (FIELD1)
There are other several usages of collate in SQL, however above mentioned are commonly used.
E) How to use collate with LIKE operator
here is an example of collate using like operation
SELECT * FROM EMP WHERE COLLATE (NAME,") Like%ABC%
Learn more about snowflake here -