sql server 2008, stored procedure

I have one table in sqlserver2008. with two fields, name and rollno.

name varchar(100),
rollno varchar(50)

though i want to store numbers in rollno i have kept it as varchar(50). now my requiement is i want to sort all records based on rollno just like numbers.

say eg.

rollno
1
2
3
4
5
6
7
8
9
10
11

instead of
1
10
11
2
3
.....

how do i do that using sql qurey. Is ther any way to sort text fields just like numbers.
thanks
ronipatsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul JacksonSoftware EngineerCommented:
Select name, rollno
from mytable
order by cast(rollno as int)
0
Peter KipropCommented:
The above answer is correct but you may also do it as below

select Name,CONVERT(INT, rollno) rollno from mytable order by 2
0
PortletPaulfreelancerCommented:
>>though i want to store numbers in rollno i have kept it as varchar(50)
why?

bigger storage, lack of data consistency, hardships with calculations, difficulties with sorting.... the list continues

at some point you will regret the decision to store numbers as strings - and goodness knows how soon it will fail when somebody gets non-numeric data into that column and either of these

 cast(rollno as int)     or     CONVERT(INT, rollno)

fails.

If you want numbers and only numbers then convert rollno to a number data type (e.g. int)
then your sorting 'is sorted' too.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.