How to sort alphanumeric values

Question

How to sort alphanumeric values

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

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

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 More actions · Answer 2

ManoharV

SSCommitted

Points: 1730

April 9, 2009 at 10:23 pm

#975555

Thanks Dwierenga

mithun.gite SSCrazy Points: 2333 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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.....