SQL Order

Hi Im using this sql code to arrange the item ascendingly;

"Select * Table1 Order By ItemCode"

but it display like this..

1
10
2
3
4
5
6
8
9

what code should i write to make it like this?

1
2
3
4
5
6
8
9
10

If you are using ORACLE you need to write the query in the following way:

"Select * Table1 Order By to_char(ItemCode,'9999')"

if your are using ACCESS:

"Select * Table1 Order By format(ItemCode,'0000')"

If using SQL Server then...

Code:

---

"SELECT * FROM Table1 ORDER BY CAST(ItemCode AS numeric) ASC"

---

And if you cannot guarantee that this field (obviously defined as character) always has numbers in it - use:

Code:

---

SELECT * FROM TABLE1 ORDER BY RIGHT('00'+ITEMCODE,2) ASC

---

Or you could change the ItemCode field to a numeric field.