Collation order by

  • Comments posted to this topic are about the item Collation order by

  • Good question that made me think, thanks George.


  • nice question. really i am confused about collation now... any one explain about collation

    You cannot get to the top by sitting on your bottom.

  • Nice question, thanks George
    Really reitterates the detail differences in the way SQL Server handles the various collations

    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • It's surprising me!
  • I got it right. Collations are tricky business. I think this is one case where people from countries that don't use English as their primary language have an edge.

    But the reference doesn't really explain anything; it makes no effort at detailing where SOH might fall in a sorting sequence.

    Just because you're right doesn't mean everybody else is wrong.

  • SQL 2015?

  • - Thursday, January 18, 2018 2:10 AM

    SQL 2015?

    It's a special release only Steve has😛

    Just because you're right doesn't mean everybody else is wrong.

  • Thank you for your kind comments, guys.

  • Here is a slight edit with the complete table variable declaration, just for fun.
    Thanks for the great question!

    declare @TableAsc table
      ( [Value] varchar(30)

    --Declare a table variable with a varchar column and insert the following values--
    insert @TableAsc values
    (CHAR(65) + '-Uppercase A')
    ,(CHAR(1) + '-Start of header')
    ,(CHAR(126) + '-Tilde ~')
    ,(CHAR(32) + '-Space')
    ,(CHAR(45) + '-Dash');

    SELECT *
    FROM @TableAsc
    ORDER BY Value COLLATE Latin1_General_CI_AS;

    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"

  • You are quite welcome. This query shows the sort order for all ASCII characters and is a little more useful--

    DECLARE @Asc INT = 0
    DECLARE @TableAsc TABLE(AscVal INT, CharVal CHAR(1))

    WHILE @Asc <= 127 BEGIN
      INSERT @TableAsc VALUES(@Asc, char(@Asc))
      SET @Asc = @Asc + 1

    SELECT *
    FROM @TableAsc
    ORDER BY CharVal COLLATE Latin1_General_CI_AS

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply