What does COLLATE LATIN1_GENERAL_CS_AI do?

Question

I’m asking this question because I came across this reply by @Sorack; I didn’t understand what influence COLLATE LATIN1_GENERAL_CS_AI had the result, I’ve seen some answers here on Stackoverflow and I didn’t understand the real use of it.

• What does it mean COLLATE LATIN1_GENERAL_CS_AI?
• Where this is applicable?
• What does it do?

I believe an example would facilitate understanding.

Answer 1

According to the collate documentation (translated by Google) is:

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

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

By separating the COLLATE LATIN1_GENERAL_CS_AI, we have the following functions.

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

CS: Specifies how Case Sensitive;

AI: Specifies how Accent Insensitive.

We also have other options, such as:

CI: Specifies how Case Insensitive.

AS: Specifies how Accent Sensitive.

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 if there is a word Voce anywhere in the sentence.

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

In other cases, where I am using CS and AS, the results are False, because I want him to consider the accents and differentiate between upper and lower case.

If you want to read more about this, see some links below:

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