Collation Functions - COLLATIONPROPERTY (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns the property of a specified collation in SQL Server 2017.

Topic link icon Transact-SQL Syntax Conventions

Syntax

COLLATIONPROPERTY( collation_name , property )  

Arguments

collation_name
Is the name of the collation. collation_name is nvarchar(128), and has no default.

property
Is the property of the collation. property is varchar(128), and can be any one of the following values:

Property name Description
CodePage Non-Unicode code page of the collation. Please see Appendix G DBCS/Unicode Mapping Tables and Appendix H Code Pages to translate these values and see their character mappings.
LCID Windows LCID of the collation. Please see LCID Structure to translate these values (you will need to convert to varbinary first).
ComparisonStyle Windows comparison style of the collation. Returns 0 for all binary collations, both (_BIN) and (_BIN2), as well as when all properties are sensitive. Bitmask values:

Ignore case : 1

Ignore accent : 2

Ignore Kana : 65536

Ignore width : 131072

Note: Even though it affects the comparison behavior, the variation-selector-sensitive (_VSS) option is not represented in this value.
Version The version of the collation, derived from the version field of the collation ID. Returns an integer value between 0 and 3.

Collations with "140" in the name return 3.

Collations with "100" in the name return 2.

Collations with "90" in the name return 1.

All other collations return 0.

Return types

sql_variant

Examples

SELECT COLLATIONPROPERTY('Traditional_Spanish_CS_AS_KS_WS', 'CodePage');  

Here is the result set.

1252   

Azure SQL Data Warehouse and Parallel Data Warehouse

SELECT COLLATIONPROPERTY('Traditional_Spanish_CS_AS_KS_WS', 'CodePage')  

Here is the result set.

1252   

See also

sys.fn_helpcollations (Transact-SQL)