Article ID: 305704 - Last Review: October 3, 2003 - Revision: 3.2

PRB: Windows Collations Ignore Single Quote and Hyphen When Ordering

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q305704
Expand all | Collapse all

SYMPTOMS

A single quote (') or an apostrophe (') and hyphen (-) are considered last when SQL Server 2000 sorts data stored with Microsoft Windows collations. To illustrate this behavior consider the following example: 
CREATE TABLE t1(c1 VARCHAR(10) COLLATE Latin1_General_CI_AI)

INSERT INTO t1 VALUES ('car''s')
INSERT INTO t1 VALUES ('cars')
INSERT INTO t1 VALUES ('carps')
INSERT INTO t1 VALUES ('car-s')
GO
SELECT * FROM t1 ORDER BY c1 ASC
GO
The output is: 
c1         
---------- 
carps
cars
car's
car-s

(4 row(s) affected)
Back to the top

CAUSE

In collation comparisons that use Windows collations, characters like a single quote (') or hyphen (-) are compared last, only after the regular alphabet characters are compared. For example, when SQL Server compares "car's" and "cars", SQL Server compares the alphabet characters first:

"cars and "cars"

Because those two are equal, the punctuation character (') is included in the comparison. The first string contains punctuation characters, whereas the second string does not. Thus, "car's" is greater than "cars". Similarly, "car's" is greater than "carps" but here SQL Server does not account for the punctuation character at all because the regular alphabet characters are already different:

"cars" is greater than "carps"

This behavior is the same as the "word sort" comparison routine in Microsoft Windows NT and is by design.
Back to the top

WORKAROUND

To work around this behavior, use a binary comparison collation. For example, use collation Latin1_General_Bin. Then, in the ORDER BY clause wrap the field in the LOWER or UPPER function. Use of the LOWER or UPPER function allows a binary ordering of the apostrophe and hyphen characters in an alphabetic/dictionary order of characters, while retaining letter case in the result set.

For example: 
CREATE TABLE t1 (c1 varchar (10) COLLATE Latin1_General_Bin NULL )
GO
INSERT INTO t1 VALUES ('CARs')
INSERT INTO t1 VALUES ('car''s')
INSERT INTO t1 VALUES ('carps')
GO
SELECT * FROM t1 ORDER BY LOWER(c1) ASC
Here is the output: 
c1         
---------- 
car's
car-s
cars
CARs

(4 row(s) affected)
Back to the top
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Back to the top
Keywords: 
kbprb KB305704
Back to the top