What does COLLATE LATIN1_GENERAL_CS_AI do?

According to the COLLATE documentation is

  

COLLATE is a clause that can be applied to a database definition or to a column definition to define the collation or a string expression to apply a collation cast.

In short, specify the character set and rules you are using.

By separating COLLATE LATIN1_GENERAL_CS_AI , we have the following functions.

LATIN1 : Defines the charset that will be used. You can do this for the bank or by query as shown in the response you posted.

CS: Specifies how Case Sensitive ;

AI: Specifies Insensitive Accent.

We also have other options, such as:

CI: Specifies as Case Insensitive .

AS: Specifies as Sensitive Accent .

BIN: Specifies the sort order to be used as binary.

But what does it all mean?

See the code below:

DECLARE @texto varchar(50);
SET @texto = 'Olhe VOCÊ, está querendo aprender sobre COLLATES?';

--Retorno: True
 SELECT CASE WHEN @texto LIKE '%voce%' COLLATE Latin1_general_CI_AI THEN  'True' ELSE 'False' END
 --Retorno False
 SELECT CASE WHEN @texto LIKE '%voce%' COLLATE Latin1_general_CS_AI THEN  'True' ELSE 'False' END
 --Retorno: False
 SELECT CASE WHEN @texto LIKE '%voce%' COLLATE Latin1_general_CI_AS THEN  'True' ELSE 'False' END

In all cases I'm looking for a word voce anywhere in the sentence.

Note that in the first SQL I'm using AI (Accent Insensitie), that is, accents do not interest me and CI (Case Insensitive), meaning I do not care if they have uppercase or lowercase characters. And with that my result will be True , because in the text I have the word YOU in the text.

In other cases, where I'm using CS and AS , the results are False , since I want it to consider the accents and case sensitivity.

If you'd like to read more about it, here are some links:

• SQL Server Collation Name
• Collations
• What does 'COLLATE SQL_Latin1_General_CP1_CI_AS' do?