How to sort alphanumeric values

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    dwierenga (4/9/2009)


    It seems like all the function calls are a bit overkill IMO, unless I'm missing how they handle some edge-case.

    A simple

    ORDER BY len(column), column usually works for me for the generic case of "sorting alphanumeric values by numeric sorting".

    Hi dwierenga

    Cool solution! 🙂

    Greets

    Flo

  • ManoharV

    SSCommitted

    Points: 1730

    Thanks Dwierenga

  • mithun.gite

    SSCrazy

    Points: 2333

    dwierenga (4/9/2009)


    It seems like all the function calls are a bit overkill IMO, unless I'm missing how they handle some edge-case.

    A simple

    ORDER BY len(column), column usually works for me for the generic case of "sorting alphanumeric values by numeric sorting".

    hey dwierenga,

    Its really fantastic and the simplest solution... !!!!!

    good man...

    Thanks from me also...

    Mithun

  • arun.sas

    SSChampion

    Points: 11831

    Hi,

    Fine with other statement,

    Actually in OP the order by based on the last No of the field

    Not the length of the field

    declare @abc table

    (

    name1 varchar(10)

    )

    insert into @abc values ('NAME1')

    insert into @abc values ('NAMENAME2')

    insert into @abc values ('NAME10')

    --

    select * from @abc

    ORDER BY cast (substring(name1,(PATINDEX('%[0-9]%',name1)),len(name1))as int)

    RESULT

    name1

    NAME1

    NAMENAME2

    NAME10

    select * from @abc

    ORDER BY len(name1), name1

    RESULT

    name1

    NAME1

    NAME10

    NAMENAME2

    ARUN SAS

  • mithun.gite

    SSCrazy

    Points: 2333

    arun.sas (4/9/2009)


    Hi,

    Fine with other statement,

    Actually in OP the order by based on the last No of the field

    Not the length of the field

    declare @abc table

    (

    name1 varchar(10)

    )

    insert into @abc values ('NAME1')

    insert into @abc values ('NAMENAME2')

    insert into @abc values ('NAME10')

    --

    select * from @abc

    ORDER BY cast (substring(name1,(PATINDEX('%[0-9]%',name1)),len(name1))as int)

    RESULT

    name1

    NAME1

    NAMENAME2

    NAME10

    select * from @abc

    ORDER BY len(name1), name1

    RESULT

    name1

    NAME1

    NAME10

    NAMENAME2

    ARUN SAS

    yes true i also tried that way and the logic of len() crashed when the alphanumeric string has no fix no of characters....so manohar u have to consider this point ...

    Good one.....

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

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