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.
Go to Top of Page
   

- Advertisement -