Post reply

Problem with the ORDER BY and "-" char

  • January 2, 2007 at 1:52 am

    #122208

    I have this code:

    declare

    @tabla table(field char(20))

    insert

    @tabla values ('AABBZ')

    insert

    @tabla values ('AADDZ')

    insert

    @tabla values ('AA-EZ')

    insert

    @tabla values ('AA--Z')

    insert

    @tabla values ('AA++Z')

    insert

    @tabla values ('AA+EZ')

    insert

    @tabla values ('AACCZ')

    select

    * from @tabla order by field

    when executed return this results:

    AA++Z              

    AA+EZ              

    AABBZ              

    AACCZ              

    AADDZ              

    AA-EZ              

    AA--Z

    I don't know why de AA-EZ and AA--Z strings are in the bottom instead off the top like AA++Z and AA+EZ, anyone know why?? or how fix it??

  • January 2, 2007 at 3:21 am

    #680710

    Hi MLopez,

    Use this below mentioned query to fix your problem :

    select * from @tabla order by

    (Case   When field like '%-%' then '0'

     When field like '%--%' then '1'

     else  field end

    ) asc

     

    Amit Gupta..

    MVP

    /* Problem makes man perfact */

     

  • January 2, 2007 at 7:36 am

    #680743

    Sorting is affected by collation, e.g.

    Latin1_General_CI_AI is will give the results you posted

    whereas SQL_Latin1_General_CP1_CI_AI will give (I assume) the desired results as in

    select * from @tabla order by field collate SQL_Latin1_General_CP1_CI_AI

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 2, 2007 at 9:11 am

    #680769

    _ is a wildcard as well.

  • January 2, 2007 at 2:26 pm

    #680835

    thx for your responses.

    now i see the collate is affecting my order, but i can't change the collate and all the queries that order in some char field in the application could be affected so don't know how can avoid this...

  • January 2, 2007 at 2:32 pm

    #680836

    You can change collation for this particular column.

    _____________
    Code for TallyGenerator

  • January 2, 2007 at 9:42 pm

    #680893

    Or...

    declare @tabla table(field char(20))

    insert @tabla values ('AABBZ')

    insert @tabla values ('AADDZ')

    insert @tabla values ('AA-EZ')

    insert @tabla values ('AA--Z')

    insert @tabla values ('AA++Z')

    insert @tabla values ('AA+EZ')

    insert @tabla values ('AACCZ')

    SELECT * FROM @tablaORDER BY REPLACE(REPLACE(field,'-','1'),'+','2')

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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