Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Well, this is a new one on me...

    Code:
    declare	@TestTable table (TestValue char(2))
    declare	@NTestTable table (TestValue Nchar(2))
    
    --load the char values
    insert into @TestTable (TestValue) select 'AA' union select 'BB' union select '-C' union select 'DD'
    
    --load the char values
    insert into @NTestTable (TestValue) select 'AA' union select 'BB' union select '-C' union select 'DD'
    
    --Char sort works fine
    select	TestValue
    from	@TestTable
    order by TestValue
    
    --NChar sort ignores dash!
    select	TestValue
    from	@NTestTable
    order by TestValue
    What gives? Something to do with the collation? Why would unicode ignore dashes in sorting?
    Last edited by blindman; 09-07-06 at 14:52.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    OK, now that is weird. If it makes you feel better, it is more consistent on SQL 2005. Just the way you don't want, though.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What? I didn't try it on 2005. Are you saying the dash is ignored by 2005 in both cases?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Well, that exercise can be left to the interested reader, but yes. I am saying that. Have a look at this article:
    http://support.microsoft.com/?id=322112

    A PITA to find, but this paragraph seems to describe your finding:
    Quote Originally Posted by Micro$oft
    A SQL collation's rules for sorting non-Unicode data are incompatible with any sort routine that is provided by the Microsoft Windows operating system; however, the sorting of Unicode data is compatible with a particular version of the Windows sorting rules. Because the comparison rules for non-Unicode and Unicode data are different, when you use a SQL collation you might see different results for comparisons of the same characters, depending on the underlying data type. For example, if you are using the SQL collation "SQL_Latin1_General_CP1_CI_AS", the non-Unicode string 'a-c' is less than the string 'ab' because the hyphen ("-") is sorted as a separate character that comes before "b". However, if you convert these strings to Unicode and you perform the same comparison, the Unicode string N'a-c' is considered to be greater than N'ab' because the Unicode sorting rules use a "word sort" that ignores the hyphen.
    Unfortunately, it appears more as you say. That the hyphen is outright ignored. I have tried it as N'-D', and N'-A' with the same results.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    OK. Now here is another twist. If you do not have the default collation, you get this:
    Code:
    TestValue 
    --------- 
    -C
    AA
    BB
    DD
    
    TestValue 
    --------- 
    -C
    AA
    BB
    DD
    
    DefaultCollation             
    ----------------------------------
    SQL_Latin1_General_CP850_BIN2

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nice find on the documentation. So now we know that is it not a "bug", but a "feature"!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    They're all features in SQL Server

    SELECT @x = name FROM sysobjects

    Would explode in any other RDBMS

    DB2 would return a SQL Cond Code of -811 I believe
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •