Post reply

Sorting by character field

  • February 6, 2009 at 12:13 pm

    #134326

    Hi,

    Let's say I want to sort my results by char(3) field, and the values are:

    FieldA

    =====

    'www'

    'xxx'

    'yyy'

    'zzz'

    '{{{'

    So, I select as follows

    SELECT * FROM ... ORDER BY FieldA

    expecting 'www' to be at the top and '{{{' at the bottom

    but in fact, it gives me

    '{{{'

    'www'

    'xxx'

    'yyy'

    'zzz'

    with curly brackets at the top

    Now, ASCII('{') is of course greater than ASCII('z'), and I always thought it sorts characters by ASCII, but it obviously not, so how does it do sorting?

    Thank you

  • February 6, 2009 at 12:24 pm

    #939730

    When ordering a string, it uses alphabetical order.

    NULLs are the lowest value,

    then special characters,

    then numbers,

    then letters.

    Greg

  • February 6, 2009 at 12:42 pm

    #939743

    Thanks Greg, but I need that "curly brackets" to be the last in the resultset, so what character should I use instead of "{"?

  • February 6, 2009 at 1:02 pm

    #939751

    SQL won't sort it that way by default because '{{{' comes before 'www' alphabetically. You could sort by ASCII code like you suggest, although I can't guarantee that it won't cause other sort issues related to case sensitivity. (i.e. A = 65, a = 97) Anyway going by your given data only, this will give the order that you want. SELECT * FROM .... ORDER BY ASCII(SUBSTRING(FieldA, 1, 1))

    Greg

  • February 6, 2009 at 1:13 pm

    #939753

    G² (2/6/2009)

    SQL won't sort it that way by default because '{{{' comes before 'www' alphabetically. You could sort by ASCII code like you suggest, although I can't guarantee that it won't cause other sort issues related to case sensitivity. (i.e. A = 65, a = 97) Anyway going by your given data only, this will give the order that you want. SELECT * FROM .... ORDER BY ASCII(SUBSTRING(FieldA, 1, 1))

    Greg

    Thanks Greg,

    But unfortunately, ordering by ASCII(...) is not an option here, i need to sort by the whole field, not just by its first character. Now, here is where I am confused:

    As you said, even if ASCII("{") > ASCII("w") , but "{" still comes before "w" alphabetically - so the question again is - it's obviously using some kind of character mapping, so how exactly does it work?

    Thanks again

  • February 8, 2009 at 1:03 pm

    #940184

    [font="Verdana"]It is, as you suspected, using "some sort of character mapping". In SQL Server terminology, this is known as the "collation". The collation defines not only the set of characters (more to do with varchar fields than nvarchar) but also the sorting rules.

    Here's the SQL Server Books Online link to read up on collations.

    Working With Collations

    My suggestion would be to find a collation that has the sorting rules you wish, then cast the column to that collation before you sort.

    Er, fair warning: I haven't tried doing that! So it may not work. Has anyone else out there done something like this?

    Edited to add: I ended up reading an interesting article by Linchi Shea related to this issue. String comparison: binary vs. dictionary. It shows how to use the different collate options too.

    [/font]

  • February 10, 2009 at 2:03 pm

    #941645

    Using Latin1_General_BIN seems to make a trick

    Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

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