Skip to content

collation-functions-collationproperty-transact-sql.md

Latest commit

 

History

History
81 lines (61 loc) · 3.77 KB

collation-functions-collationproperty-transact-sql.md

File metadata and controls

81 lines (61 loc) · 3.77 KB
title COLLATIONPROPERTY (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 10/24/2017
ms.prod sql
ms.prod_service database-engine, sql-database, sql-data-warehouse, pdw
ms.reviewer
ms.technology t-sql
ms.topic language-reference
f1_keywords
COLLATIONPROPERTY_TSQL
COLLATIONPROPERTY
dev_langs
TSQL
helpviewer_keywords
collations [SQL Server], properties
COLLATIONPROPERTY function
ms.assetid f5029e74-a1db-4f69-b0f5-5ee920c3311d
author MikeRayMSFT
ms.author mikeray
monikerRange >=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Collation Functions - COLLATIONPROPERTY (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

This function returns the requested property of a specified collation.

Topic link icon Transact-SQL Syntax Conventions

Syntax

COLLATIONPROPERTY( collation_name , property )

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');

[!INCLUDEssResult]

1252

[!INCLUDEssSDWfull] and [!INCLUDEssPDW]

SELECT COLLATIONPROPERTY('Traditional_Spanish_CS_AS_KS_WS', 'CodePage')

[!INCLUDEssResult]

1252

See also

sys.fn_helpcollations (Transact-SQL)