Collation order by

  • GeorgeCopeland

    SSCertifiable

    Points: 6896

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

  • HappyGeek

    SSCoach

    Points: 18670

    Good question that made me think, thanks George.

    ...

  • manik_anu

    SSCrazy

    Points: 2367

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

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

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71483

    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”

  • Carlo Romagnano

    SSC-Insane

    Points: 21830

    It's surprising me!
  • Rune Bivrin

    SSCertifiable

    Points: 7614

    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.

  • ildjarn.is.dead

    Right there with Babe

    Points: 717

    SQL 2015?

  • Rune Bivrin

    SSCertifiable

    Points: 7614

    ildjarn.is.dead - 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.

  • GeorgeCopeland

    SSCertifiable

    Points: 6896

    Thank you for your kind comments, guys.

  • webrunner

    One Orange Chip

    Points: 29987

    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 says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • GeorgeCopeland

    SSCertifiable

    Points: 6896

    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
    END

    SELECT *
    FROM @TableAsc
    ORDER BY CharVal COLLATE Latin1_General_CI_AS

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

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