###COLLATION_SORT_KEY

Description

The COLLATION_SORT_KEY function generates a sort key for the input string based on the specified character set, which is used to sort the string according to the rules of the particular character set.

Syntax

collection_sort_key(str, coll)

• str: The input string.
• coll: The specified character set.

Return Value

Returns a binary value representing the sort key generated for the input string according to the specified character set.

Example

1. Sort Chinese strings according to pinyin order:

SELECT s, collation_sort_key(s, 'zh') AS sort_key
FROM ('你好', '苹果', '香蕉', '梨子', '草莓', '西瓜', '世界') AS t(s)
ORDER BY sort_key;

The result is as follows:

s   | sort_key
----|----------
你好 | 7A96647001060106
苹果 | 7DA963B401060106
梨子 | 71FDA16101060106
草莓 | 5496779301060106
西瓜 | 8EAD628E01060106
世界 | 86056B8E01060106
香蕉 | 90A36AB101060106

2. Sort English strings according to the English character set:

SELECT s, collation_sort_key(s, 'en') AS sort_key
FROM ('apple', 'banana', 'cherry', 'grape', 'orange', 'strawberry') AS t(s)
ORDER BY sort_key;

The result is as follows:

s    | sort_key
-----|----------
apple | 0100000001000000
banana | 0100000002000000
cherry | 0100000003000000
grape | 0100000004000000
orange | 0100000005000000
strawberry | 0100000010000000

Notes

• The COLLATION_SORT_KEY function is only applicable to string type inputs.
• The input character set must be valid, otherwise NULL will be returned.
• The generated sort key is a binary value that can be used for sorting in the ORDER BY clause.