ordering a character coulmn as if it was numeric

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 2000 Forums

Transact-SQL (2000)

ordering a character coulmn as if it was numeric

Author

Topic

hugh_mungo
Starting Member

10 Posts

Posted - 2006-03-16 : 06:21:04

Hi,

I have a character column in a table that contains values such as
0-50
50-100
100-150
150-200
200-250

When I order on this column they results come out as
0-50
100-150
150-200
200-250
50-100

is there a way I can order them numerically using sql?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-16 : 06:41:12

Why did you store the range in varchar column?
It will be easy to maintain if you have two columns range1 and range2 with int datatypes

One of the tricky ways is

select * from yourTable order by len(col),col

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-16 : 06:59:27

[code]select col
from
(
select '0-50' as col union all
select '50-100' as col union all
select '100-150' as col union all
select '150-200' as col union all
select '150-220' as col union all
select '200-250' as col
) c
order by convert(int, left(col, charindex('-', col) - 1)), col[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources