# 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

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

• 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)