how to sort varchar column which contains numbers

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

Transact-SQL (2005)

how to sort varchar column which contains numbers

Author

Topic

SK2412
Starting Member

2 Posts

Posted - 2014-12-02 : 04:31:25

Hi,

I have a column with values 1,1A,1B,2,11,2A,2B,22,3 so on.

I want to sort this column in this way:
1,1A,1B,11,2,2A,2B,22,3,3A,3B,3C,33,34,4

how can i achieve this?
Please help as this is an urgent issue

Thanks,
SK

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 09:33:27

This works:


declare @t table (c char(2))
insert into @t(c) values
('1'),('1A'),('1B'),('11'),('2'),('2A'),('2B'),('22'),('3'),('3A'),('3B'),('3C'),('33'),('34'),('4')

select * from @t --order by c
order by left(c,1), 
    case 
	  when len(c) = 1 then 0
	  when ISNUMERIC(right(c,1)) = 1 then right(c,1) 
	  else ascii(right(c,1)) - ascii('A')
	end

It assumes that the input columns are char(2) and that the second character, if present, is either a digit or an uppercase letter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources