sorting numbers before letters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sorting numbers before letters

Author

Topic

progoal
Starting Member

5 Posts

Posted - 2012-09-18 : 16:28:30

hi guys i have a problem, i want to select numbers before letters in the same column,:

FM0001
AS003
ER0005
OJ001
UG0002

i want something like that

FM0001
UG0002
ER0005
OJ001
AS003

can someone help me?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 16:33:28

can you explain how OJ came before AS?

if you had considered only number part for sorting why FM0001 etc are after AS003?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

progoal
Starting Member

5 Posts

Posted - 2012-09-18 : 17:00:32

can you explain how OJ came before AS?
i dont want order by letters only by numbers so if oj came before doesn't matter.. only the number part

if you had considered only number part for sorting why FM0001 etc are after AS003?
sorry i made a confusion. ill edit that

btw sorry my english:P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 17:23:38

still your ordering is not proper

how will 1 come after 5?

as per your explanation you should be getting below as output

OJ001
FM0001
UG0002
AS003
ER0005

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

progoal
Starting Member

5 Posts

Posted - 2012-09-19 : 05:45:48

yes maybe is that i mistake about "00" and "000"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-19 : 11:45:04

so are you telling you dont know what you're after?
confirm whats the exact output you want and basis for it
then we'll help with queries

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

progoal
Starting Member

5 Posts

Posted - 2012-09-19 : 16:28:06

i want a order by ASC only with numbers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 11:47:41

the what you can do is something like

ORDER BY SUBSTRING(field,PATINDEX('%[0-9]%',Field),LEN(Field))*1 ASC

i assume format is consistent ie always alphabets followed by numbers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

progoal
Starting Member

5 Posts

Posted - 2012-09-20 : 14:14:54

thank u for reply i solved the problem..
with order by LEN(field), substring(field, PATINDEX('%[0-9]%', field), LEN(field)

:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 23:14:11

oh so you wanted to consider length as well
you had not specified it in your requirement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources