order by and alphanumeric character

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)

order by and alphanumeric character

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-27 : 07:43:48

Phil writes "how do you order by an alphanumeric column. One such as:

Column
------
1
2
3a
3b
4
5
6
7
8
9
10
11a
11b

etc.

Thanks,
Phil"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-01-27 : 09:26:58

[code]create table test ( data varchar(10))

insert into test
select '1'
union all select '2'
union all select '7'
union all select '8'
union all select '9'
union all select '10'
union all select '3a'
union all select '3b'
union all select '4'
union all select '5'
union all select '6'
union all select '11a'
union all select '11b'

select data
from test
order by case when isnumeric(right(data,1))=0 then convert(int,left(data,len(data)-1)) else data end

drop table test
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources