Collation can be specified at the server level, database level, column level, expression level, and the identifier level. A different method is required for each of these.
Here’s how to find the server level collation:
SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';
This returns the collation like this:
Server Collation ---------------------------- SQL_Latin1_General_CP1_CI_AS
In PostgreSQL, we can use the pg_collation catalog to get a list of the available collations. Another way to do it is to use the \dOS command.
Using the first method, we can run the following statement to return a list of available collations in PostgreSQL:
SELECT * FROM pg_collation;
These collations are mappings from an SQL name to operating system locale categories.
Running the following statement lists all collations supported by the server in MySQL:
SHOW COLLATION;
However, the resulting list is quite long, and if you have a collation in mind, you can always filter the list with either the LIKE clause or the WHERE clause.
In database systems, Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.
For example, when you run a query using the ORDER BY clause, collation determines whether or not uppercase letters and lowercase letters are treated the same.
Collation is also used to determine how accents are treated, as well as character width and Japanese kana characters. Collation can also be used to distinguish between various ideographic variation selectors in certain collations (such as the Japanese_Bushu_Kakusu_140 and Japanese_XJIS_140 collations that were introduced in SQL Server 2017).
Different database management systems will provide different collation options. Depending on the DBMS, collation can be specified at the server level, the database level, the table level, and the column level. Collations can also be specified at the expression level (so you can specify which collation to use when you run a query), and at the identifier level.
Continue reading
Below is a table of all collations supported in SQL Server 2017 (warning: It’s a long list!).
This list was returned by running the following code:
SELECT name, description FROM sys.fn_helpcollations();
That returns the following list of 3955 collations.
Continue reading