Geek Answers Handbook

Weird SQL Server 2005 The difference between sorts between varchar () and nvarchar ()

Can someone explain this:

SELECT 
  CASE WHEN CAST('iX' AS nvarchar(20)) 
      > CAST('-X' AS nvarchar(20)) THEN 1 ELSE 0 END,
  CASE WHEN CAST('iX' AS varchar(20)) 
      > CAST('-X' AS varchar(20)) THEN 1 ELSE 0 END

Results: 0 1

SELECT 
  CASE WHEN CAST('i' AS nvarchar(20)) 
      > CAST('-' AS nvarchar(20)) THEN 1 ELSE 0 END,
  CASE WHEN CAST('i' AS varchar(20)) 
      > CAST('-' AS varchar(20)) THEN 1 ELSE 0 END

Results: 1 1

In the first query, the result is nvarchar()not what I expect, but the deletion Xdoes the sorting nvarchar()as expected.

(My original queries used syntax ''and N''literal to distinguish between varchar()and nvarchar(), and not CAST(), got the same result.)

Set up sorting for the database SQL_Latin1_General_CP1_CI_AS.

+5
source share
2 answers

. " ", . , .

-X iX, X iX, -X, , . "-" "i", "" "i", "i", .

MSDN,

SQL , Unicode, , Microsoft Windows ; , Unicode Windows . , Unicode Unicode , SQL , , . , SQL- "SQL_Latin1_General_CP1_CI_AS", -Unicode 'a-c' , 'ab', ( "-" ) , "b". , , , Unicode N'a-c ' , N'ab ' " ", .

SELCT body From MSDN_Articles WHERE url IN ( " http://support.microsoft.com/kb/322112" )

+9

!

, , . "" '' 'X', '-X'.

, : SQL-

+1

More articles:


All Articles