Collections:
COLLATION() - Detecting Character Collation Name
How to detect the character collation name associated to a given character string using the COLLATION() function?
✍: FYIcenter.com
COLLATION(str) is a MySQL built-in function that returns the character collation name associated to a given character string. A character collation name refers to a set of rules to sort a set of characters. For example:
SELECT COLLATION('FYI'); -- +------------------+ -- | COLLATION('FYI') | -- +------------------+ -- | utf8_unicode_ci | -- +------------------+ SELECT COLLATION(_latin1'FYI'); -- +-------------------------+ -- | COLLATION(_latin1'FYI') | -- +-------------------------+ -- | latin1_swedish_ci | -- +-------------------------+ CREATE TABLE MyTable (comment CHAR(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci); INSERT INTO MyTable (comment) VALUES ('I like it!'); INSERT INTO MyTable (comment) VALUES ('Good job1'); SELECT comment, COLLATION(comment) FROM MyTable; -- +------------+--------------------+ -- | comment | COLLATION(comment) | -- +------------+--------------------+ -- | I like it! | utf8mb4_general_ci | -- | Good job1 | utf8mb4_general_ci | -- +------------+--------------------+
Note that the character collation name has a direct impact on character string comparison operations. For example:
SET @str1 = 'fyicenter.com', @str2 = 'FYIcenter.com'; SELECT @str1=@str2, COLLATION('@str1'), COLLATION('@str2'); -- +-------------+--------------------+--------------------+ -- | @str1=@str2 | COLLATION('@str1') | COLLATION('@str2') | -- +-------------+--------------------+--------------------+ -- | 1 | utf8_unicode_ci | utf8_unicode_ci | -- +-------------+--------------------+--------------------+ SET @str1 = _latin1'fyicenter.com' COLLATE latin1_general_cs; SET @str2 = _latin1'FYIcenter.com' COLLATE latin1_general_cs; SELECT @str1=@str2, COLLATION('@str1'), COLLATION('@str2'); -- +-------------+--------------------+--------------------+ -- | @str1=@str2 | COLLATION('@str1') | COLLATION('@str2') | -- +-------------+--------------------+--------------------+ -- | 0 | utf8_unicode_ci | utf8_unicode_ci | -- +-------------+--------------------+--------------------+
Reference information of the COLLATION() function:
COLLATION(str): name Returns the character collation name associated to a given character string. Arguments, return value and availability: str: Required. The character string to be examined. name: Return value. The character collation name. Available since MySQL 4.0.
Related MySQL functions:
⇒ CONCAT() - Concatenating Strings
⇐ COERCIBILITY() - Character Collation Coercibility
2023-12-19, 222🔥, 0💬
Popular Posts:
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...
How To Select All Columns of All Rows from a Table with a SELECT statement in SQL Server? The simple...
How To Provide Default Values to Function Parameters in SQL Server Transact-SQL? If you add a parame...
How to set the current database in SQL Server? Once you are connected to the SQL Server, you should ...
How To Convert Characters to Numbers in Oracle? You can convert characters to numbers by using the T...