ORDER BY issue, with numeric values stored in char.

How to use order by on integer values stored in a char. field ?

This is a SQL problem observed many times, when we need to order by ( sort ) numeric values which are stored in char field. Being stored as char, some numeric values are placed before others which should not be the case, if treated as numeric.
e. g. if stored as char, 2 > 12.

Example:
create table orderTable ( col_1 varchar(10) )

insert into orderTable values ( '1' )
insert into orderTable values ( '2' )
insert into orderTable values ( '11' )
insert into orderTable values ( '12' )
insert into orderTable values ( '21' )
insert into orderTable values ( '111' )

--order without converting char to interger.
select col_1 from orderTable order by col_1

col_1
-----
1
11
111
12
2
21

Observe the output, it does'n look in order, if you considered numeric values.

The solution is pretty straight forward, you need to convert char value into a numeric, before you do order by.

select col_1 from orderTable order by convert(int, col_1)

col_1
-----
1
2
11
12
21
111

If your numeric data is a part of a string, you can use substring or other string functions, to get numeric data out, and then convert it to numeric.