SQL Server - COLLATE Examples

SQL Server - COLLATE Examples

Collate is used to display and store an international character set, based on the requirements. You can specify collation during a SELECT statement, while creating a database, while creating a table, in the WHERE clause etc. Below are few self explanatory examples related to SQL Server Collate. These are very simple examples of Collate in order to give you an idea as to how Collate works.

Lets create a table, insert few values in it and use it in some of our examples.

CREATE TABLE test
(mychar varchar(15) NOT NULL);  
GO  

INSERT test(mychar) VALUES ('ca');  
INSERT test(mychar) VALUES ('ch');
INSERT test(mychar) VALUES ('ci');
GO  

Select * from test

mychar
ca
ch
ci

Example 1 of Collate

Using Collate in the SELECT Statement

Applying typical collation

SELECT mychar FROM test  
ORDER BY mychar 
COLLATE Latin1_General_CS_AS_KS_WS ASC;  

mychar
ca
ch
ci

Example 2 of Collate

Applying Spanish collation

SELECT mychar FROM test  
ORDER BY mychar 
COLLATE Traditional_Spanish_ci_ai ASC;  

mychar
ca
ci
ch

Example 3 of Collate

Using Collate in the CREATE TABLE Clause

CREATE TABLE test1
  (mychar varchar(15)
   COLLATE Traditional_Spanish_ci_ai NOT NULL  
  );  
GO  

INSERT test1(mychar) VALUES ('ca');  
INSERT test1(mychar) VALUES ('ch');
INSERT test1(mychar) VALUES ('ci');
GO  

Select * from test1
ORDER BY mychar ASC

mychar
ca
ci
ch

If you try to do it without Collate in the Create Table Clause then the results will be different:

CREATE TABLE test2
  (mychar varchar(15) NOT NULL  
  );  
GO  

INSERT test2(mychar) VALUES ('ca');  
INSERT test2(mychar) VALUES ('ch');
INSERT test2(mychar) VALUES ('ci');
GO  

Select * from test2
ORDER BY mychar ASC

mychar
ca
ch
ci

You can use Collate with Alter Table Clause also.

Example 4 of Collate

Using Collate in the WHERE Clause

CREATE TABLE test3
  (mychar varchar(15)
   
  );  
GO  

INSERT test3(mychar) VALUES ('case');  
INSERT test3(mychar) VALUES ('Case');
INSERT test3(mychar) VALUES ('CASE');
GO  

Select * from test3

mychar
case
Case
CASE

SELECT *
FROM test3
WHERE mychar = 'Case'

mychar
case
Case
CASE

SELECT *
FROM test3
WHERE mychar COLLATE Latin1_General_CS_AS = 'Case'

mychar
Case

Example 5 of Collate

Casting the Collation

Here nvarchar is being converted into varchar

SELECT SQL_VARIANT_PROPERTY(N'abc','BaseType') BaseType

BaseType
nvarchar

SELECT SQL_VARIANT_PROPERTY(
(SELECT CAST(N'abc' AS varchar(5)) 
COLLATE French_CS_AS),'BaseType') BaseType1

BaseType1
varchar

Example 6 of Collate

Using CONVERT with COLLATE

SELECT
CONVERT(varchar(50), N'æøåáäĺćçčéđńőöřůýţžš')
COLLATE Cyrillic_General_CI_AI

(No column name)
?oaaalcccednooruytzs

[Source of Example 6]