SQL Server - Collation

Card Puncher Data Processing

SQL Server - Collation

About

collation in Sql server. locale (Character set) in SQL Server

Syntax

SQL_Latin1_General_CP1_CI_AS

where:

  • SQL_latin1_General = Collation Designator
    • The SQL is a prefix for old collations created prior the use of OS-level Collations
    • Defines the dictionary (therefore the sorting rules).
  • CP1 = Code Page 1252
  • CI = Case insensitive comparisons (a=A)
  • AS = accent sensitive, so 'é' does not equal 'e'
  • WS = width sensitive
  • KS = kanatype sensitive

See doc: Windows Collation Name structure

Management

List

select 
    name, 
    description,
    CONVERT(varchar(100), COLLATIONPROPERTY(name, 'CodePage')) as codePage, -- Convert the variant data type to varchar in case your client does not support it
    CONVERT(varchar(100), COLLATIONPROPERTY(name, 'LCID')) as LCID, -- See doc
    CONVERT(varchar(100), COLLATIONPROPERTY(name, 'ComparisonStyle')) as ComparisonStyle, -- Ignore case : 1, Ignore accent : 2, Ignore Kana : 65536, Ignore width : 131072
    CONVERT(varchar(100), COLLATIONPROPERTY(name, 'Version')) as Version -- version of the collation
    from fn_HelpCollations();

Doc:

Windows Collations Support

SELECT * FROM sys.fn_helpcollations() WHERE name NOT LIKE 'SQL%';  

SELECT

SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;    

Example

  • SQL_Latin1_General_CP1_CI_AS: number first then letters:
  • ECBDIC characterset: COLLATE SQL_EBCDIC037_CP1_CS_AS letters first then number ?







Share this page:
Follow us:
Task Runner