ORder by [numeric then alphabetic] for varachar

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

SQL Server 2008 Forums

Transact-SQL (2008)

ORder by [numeric then alphabetic] for varachar

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-09-25 : 03:16:26

I have Data in a varchar field of my table as

1
f
g
h
10
2
3
5
4
6
7
20
8
9
30
11
21

I wants its result with by order by in the way that frist numeric record will come in order then alphabetics like

1
2
3
4
5
6
7
8
9
10
11
20
21
30
f
g
h

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Sachin.Nand

2937 Posts

Posted - 2010-09-25 : 04:20:44

Isn't just this enough?

select * from yourtable order by your ordercolumn

Does your column contain alphanumeric characters?

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-25 : 12:32:47

[code]select * from yourtable
ORDER BY CASE WHEN ISNUMERIC(yourcol)=1 THEN 1
ELSE 2
END,Len(yourcol),
yourcol asc
[/code]

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-25 : 14:17:20

quote:
select * from yourtable
ORDER BY CASE WHEN ISNUMERIC(yourcol)=1 THEN 1
ELSE 2
END,Len(yourcol),
yourcol asc

Decimals and exponents (1E38, 1E-10) could throw that off, this may be better:

select * from yourtable
ORDER BY CASE WHEN ISNUMERIC(yourcol)=1 THEN 1
ELSE 2 END,
CASE WHEN ISNUMERIC(yourcol)=1 THEN cast(yourcol as float) END,
yourcol asc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources