How to Set the Character Set and Collation of a Column in MySQL

Posted on by Ian

In MySQL, you can specify the character set and collation at various levels. You can specify them at the connection level, the server level, the database level, the table level, and the column level. You can also specify a collation in your queries so that it overrides any collation that has been previously specified at the aforementioned levels.

To set the character set and collation at the column level, you can use the CREATE TABLE statement or ALTER TABLE statement (depending on whether you’re creating the table or modifying it), and specify the character set and collation within the column’s definition (the column that you want to set the character set/collation on).
Continue reading →

How to Set the Collation of a Database in SQL Server (T-SQL)

Posted on by Ian

When using SQL Server, you can use T-SQL to specify the default collation of a database. Specifically, you can use the COLLATE clause of either the CREATE DATABASE or ALTER DATABASE  statements.

The COLLATE clause is optional – if you don’t use it when creating the database, the database will use the default collation of the server (which was specified at the time SQL Server was installed). And if you don’t specify the clause when altering the database, its default collation won’t change.
Continue reading →

How to Set the Collation of a Column in SQL Server (T-SQL)

Posted on by Ian

By default, each column in a SQL Server database uses the collation that’s been specified at the database level. And by default, the database collation is taken from the server collation. However, these collation settings can be overridden, both at the database level and the column level by explicitly setting the collation at that level.

This page demonstrates how to specify the collation of a column. You can set the collation by adding the T-SQL COLLATE clause to the CREATE TABLE and ALTER TABLE statements. When you use those statements, you define the column and its properties, including any collation settings. The COLLATE clause is optional so if you don’t include it, the column will simply use the default collation of the database.
Continue reading →

How to Show the Server Collation in SQL Server (T-SQL)

Posted on by Ian

The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, as well as any newly created user databases. The server collation is specified during SQL Server installation.

Below are two ways to check the server collation in SQL Server using Transact-SQL.

The SERVERPROPERTY() Function

One option is to use the SERVERPROPERTY() function:

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';

This returns the collation like this:

Server Collation
----------------------------
SQL_Latin1_General_CP1_CI_AS

Continue reading →

How to Show the Collation of a Database in SQL Server (T-SQL)

Posted on by Ian

Here are two ways to return the collation of a database in SQL Server using Transact-SQL.

Query sys.databases

The first option is to run a query against sys.databases to return the collation of a specific database. The WHERE clause allows you to narrow the results down to the database/s you’re interested in:

SELECT 
    name, 
    collation_name 
FROM sys.databases
WHERE name = 'Music';

This results in something like this:

name   collation_name              
-----  ----------------------------
Music  SQL_Latin1_General_CP1_CI_AS

In this case, we specified the database called Music.
Continue reading →

How to Specify the Collation in a Query in SQL Server (T-SQL)

Posted on by Ian

In database systems, collation determines how data is sorted and compared in a 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.

In SQL Server, collation is specified at the server level, the database level, and the column level.

Collation can also be applied to a character string expression to apply a collation cast. For example, you can use the COLLATE clause in a T-SQL SELECT statement to specify the collation to be used. Like this:

USE Music;
SELECT ArtistId, ArtistName
FROM Artists
ORDER BY ArtistName COLLATE Latin1_General_CI_AI;

That collation uses CI for case-insensitive, and AI for accent-insensitive.
Continue reading →

How to Show the Collation of a Column in SQL Server (T-SQL)

Posted on by Ian

In SQL Server, collation can be specified at the server level, database level, and the column level, as well as inside expressions.

To find out what collation a specific column uses, run a T-SQL query against sys.columns. Like this:

SELECT 
    name, 
    collation_name 
FROM sys.columns 
WHERE name = N'ArtistName';

This results in something like this:

name   collation_name              
----------  ----------------------------
ArtistName  SQL_Latin1_General_CP1_CI_AS

This example returns the collation of a column called ArtistName. Simply replace that with the name of the column you’d like to query.
Continue reading →

How to Show the Collation of your Connection in MySQL

Posted on by Ian

When you run a query against a MySQL database, MySQL uses a bunch of system variables to determine which character set and collation to use whenever queries are run. If the client uses a different character set to the server, then MySQL can translate it into an appropriate character set and collation.

When sending the query results back to the client, MySQL can translate these results back to a different character set altogether if required. MySQL uses system variables to determine which character sets and collations to use at each of these steps.

The following singles out the connection collation:

SELECT @@collation_connection;

Example result:

+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+

Continue reading →

How to Show the Collation of a Database in MySQL

Posted on by Ian

This article provides three ways to return the collation of a database in MySQL.

The following statement can be used to check the default character set and collation for a given database:

USE Music;
SELECT @@character_set_database, @@collation_database;

Example result:

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+

This example shows the collation for a database called Music. First, we switch to that database, then we do the SELECT statement to return system variables for the character set and the collation.
Continue reading →

How to Show the Server Collation in MySQL

Posted on by Ian

Running the following command returns the server’s default collation.

SELECT @@collation_server;

Example result:

+--------------------+
| @@collation_server |
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+

This returns the collation_server system variable, which contains the collation of the server. However, this isn’t the only way to do it.
Continue reading →