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, 227🔥, 0💬
Popular Posts:
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...
What is SQL Server Transact-SQL (T-SQL)? SQL Server Transact-SQL, also called T-SQL, is an extension...
How To Create a Dynamic Cursor with the DYNAMIC Option in SQL Server Transact-SQL? If the underlying...
How To Convert Binary Strings into Integers in SQL Server Transact-SQL? Binary strings and integers ...
How To Use GO Command in "sqlcmd" in SQL Server? "sqlcmd" is a command line client application to ru...