Collation order by

  • GeorgeCopeland


    Points: 6936

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

  • HappyGeek


    Points: 18681

    Good question that made me think, thanks George.


  • manik_anu


    Points: 2367

    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.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71808

    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


    Points: 21966

    It's surprising me!
  • Rune Bivrin


    Points: 7808

    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.


    Right there with Babe

    Points: 763

    SQL 2015?

  • Rune Bivrin


    Points: 7808 - 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


    Points: 6936

    Thank you for your kind comments, guys.

  • webrunner


    Points: 30254

    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?"

  • GeorgeCopeland


    Points: 6936

    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 11 (of 11 total)

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