Between & Collation

  • tilew-948340

    Hall of Fame

    Points: 3431

    L' Eomot InversΓ© (8/29/2013)


    Good question.

    ... in case sensitive collations ''sysygy' would sort before 'Sally', but in practice it sorts after 'Sally' regardless of whether the collation is case sensitive or not ...

    That is interesting! I did some test, to make sure I understand correctly and I got different answer depending if I specify collation or remove the "order by 1".

    insert #test ( name ) values ( 'syst');

    insert #test ( name ) values ( 'Sally');

    insert #test ( name ) values ( 'sally');

    insert #test ( name ) values ( 'Syst');

    insert #test ( name ) values ( 'Soap');

    insert #test ( name ) values ( 'soap');

    Case 1: create table #test(name varchar(10) COLLATE Latin1_General_CS_AS);

    sally

    Sally

    soap

    Soap

    syst

    Syst

    Case 2: same as Case1 but with no Collate

    Sally

    sally

    Soap

    soap

    Syst

    syst (Hugo, here is your proof that you can't rely on the order that data has been entered!)

    Case 3: same as Case 1 but removed the "order by 1" (result is as the data has been entered)

    syst

    Sally

    sally

    Syst

    Soap

    soap

    Never stop learning! Thanks!

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    Ed Wagner (8/29/2013)


    In the ASCII character set [...] A is 65 while a is 97. To have them sort differently here is somewhat counter-intuitive [...].

    Don't get me wrong here, I like that lower case sorts ahead of upper case...it's just different.

    +1, except for the fact that I don't like it. πŸ™‚

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • marlon.seton

    SSCrazy

    Points: 2563

    Hugo Kornelis (8/29/2013)


    I decided to rely on my memory of lowercase/uppercase order instead of double checking. Bad decision. πŸ˜‰

    Ditto.

  • sqlnaive

    SSCoach

    Points: 17435

    Good question. Brushed up things on sorting. πŸ™‚

  • udayroy15

    Ten Centuries

    Points: 1076

    very very tough πŸ™‚

Viewing 5 posts - 16 through 20 (of 20 total)

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