COLLATE

Represents a collation cast operation when applied to character string expression. As mentioned in the Culture and Collation section, AnalytiX-BI collations are composed by two parts, separated by underscore:

·         A culture identifier, which follows the .NET rules for culture identifiers. The name is a combination of an ISO 693 two-letter culture code associated with a language and an ISO 3166 two-letter subculture code associated with a country or region. Examples include jp-JP for Japanese (Japan) and en-US for English (United States). For more information refer to Culture names and Identifiers for .NET.

·         A case identifier, which can be one of the two possibilities:

o    ci – which stands for case-insensitive

o    cs – which stands for case-sensitive

Collation identifiers are case-insensitive so, for example, all the following identifiers are equivalent in AnalytiX-BI:

COLLATE en-US_ci

COLLATE EN-US_CI

COLLATE en-us_ci

The following query will return the row associated with the Beverages categories because the default collation is case-insensitive.

SELECT * FROM Categories WHERE CategoryName = 'beverages'

The following query will not return any row because it is forcing a case-sensitive comparison using the COLLATE keyword.

SELECT * FROM Categories WHERE CategoryName COLLATE en-us_cs = 'beverages'

The COLLATE keyword can be used after column references of type string or after string literals.