Collation Functions - COLLATIONPROPERTY (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
This function returns the requested property of a specified collation.
Transact-SQL syntax conventions
Syntax
COLLATIONPROPERTY( collation_name , property )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
collation_name
The name of the collation. The collation_name argument has an nvarchar(128) data type, with no default value.
property
The collation property. The property argument has a varchar(128) data type, and can have any one of the following values:
Property name | Description |
---|---|
CodePage | Non-Unicode code page of the collation. This is the character set used for varchar data. See Appendix G DBCS/Unicode Mapping Tables and Appendix H Code Pages to translate these values, and to see their character mappings. Base data type: int |
LCID | Windows locale ID of the collation. This is the culture used for sorting and comparison rules. See LCID Structure to translate these values (you will first need to convert to varbinary). Base data type: int |
ComparisonStyle | Windows comparison style of the collation. Returns 0 for binary collations - both (_BIN) and (_BIN2) - as well as when all properties are sensitive - (_CS_AS_KS_WS) and (_CS_AS_KS_WS_SC) and (_CS_AS_KS_WS_VSS). Bitmask values: Ignore case : 1 Ignore accent : 2 Ignore Kana : 65536 Ignore width : 131072 Note: the variation-selector-sensitive (_VSS) option is not represented in this value, even though it affects the comparison behavior. Base data type: int |
Version | The version of the collation. Returns a 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. Base data type: tinyint |
Return types
sql_variant
Examples
SELECT COLLATIONPROPERTY('Traditional_Spanish_CS_AS_KS_WS', 'CodePage');
Here is the result set.
1252
Azure Synapse Analytics and Analytics Platform System (PDW)
SELECT COLLATIONPROPERTY('Traditional_Spanish_CS_AS_KS_WS', 'CodePage')
Here is the result set.
1252
See also
sys.fn_helpcollations (Transact-SQL)
Collation and Unicode support